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.