Compute standard hours spent on weekdays by Tier, Week, Month and Country

Imagine a Sales dataset with the following columns – ID, Country, Start date, End date and Tier. Here’s a snapshot of the table:

ID	Country	Start date	End date	Tier
33948	ES	25-Sep-17	28-Sep-17	3
19820	US	08-Oct-17	17-Oct-17	4
9118	US	27-Oct-17	03-Nov-17	2
1563	ES	02-Sep-17	07-Sep-17	2
11087	US	18-Oct-17	27-Oct-17	1
15057	US	05-Nov-17	13-Nov-17	4
13567	ES	18-Oct-17	26-Oct-17	4
22362	DE	28-Aug-17	30-Aug-17	4
31301	FR	04-Nov-17	10-Nov-17	2
22838	US	05-Sep-17	13-Sep-17	4

There is another 2 column table which lists down the Tier wise standard hours. Here’s a snapshot of the table:

Tier	Daily load hh
1	0.7
2	0.72
3	0.8
4	1.2

The result which one expects from these two tables is shown below:

		Tier
Month	Week	1	2	3	4	Grand total		Slicer
9	35	x	x	x	x	x		US
	36	x	x	x	x	x		UK
	37	x	x	x	x	x		FR
	38	x	x	x	x	x		IT
	39	x	x	x	x	x		ES
Grand total		x	x	x	x	x

Let’s take a specific example to show the result expected for the US Region. From the last row of the Sales Table, one can see that there is a ID 22838 in US which spans the date range 05-Sept-17 to 13-Sep-17. These dates span week numbers 36 (from 05-Sept-17 to 08-Sep-17) and 37 (from 11-Sept-17 to 13-Sep-17) [The week numbers have been obtained by using Excel’s WEEKNUM() function]. So, is US, for Tier 4, in Week 36, the standard hours worked on weekdays would be 4.8 (4 days * 1.2 hours per day). Likewise, in week 37, the standard hours worked on weekdays would be 3.6 (3 days * 1.2 hours).

I have solved this problem using Power Query a.k.a Get & Transform (Available under Data) in Excel 2016 and PowerPivot.  You may download my solution workbook from here.