How to Reflect Hours Posted by Individual Date Under a Certain Posting Date

[Anonymous]

I have two tables that I need to compare Hours Budgeted for the month vs hours actually worked for the month. Using January as an example....

January 2020 had 3 pay periods. So it was considered a "240" hour month, as 3, 2-week payroll periods would post and have 80 hours x 3 periods = 240 hours for the month.

The budgeted table shows hours budgeted for the entire month of January, but my actual table shows hours posted on the day the work was performed. So my actual table starts on 12/15/2019, as that is the very first day counted in the very first payroll of 2020, that posted on 01/03/2020. So 12/15/19 - 12/28/19 posts on 01/03, 12/29/2019 - 01/11/2020 posts on 01/17/2020, and 01/12/2020 - 01/25/2020 posts on 01/31/2020.

I am not sure how I can go about getting dates aligned by the correct period in which they should post for these actual numbers, so that I can compare the Hours Budgeted for an employee in a given month, to the hours that actually hit in that month's payroll periods. The example above of prior year actual dates worked is not the only one. As, of course, hours from 01/26/2020 and later would be posting in February. 

This is my first post, so please let me know what I can provide/clarify to help!

Actual Day Worked	Payroll Posting Month
Sunday, December 15, 2019	Jan-20
Monday, December 16, 2019	Jan-20
Monday, December 16, 2019	Jan-20
Monday, December 16, 2019	Jan-20
Monday, December 16, 2019	Jan-20
Monday, December 16, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Tuesday, December 17, 2019	Jan-20
Wednesday, December 18, 2019	Jan-20
Wednesday, December 18, 2019	Jan-20
Wednesday, December 18, 2019	Jan-20
Wednesday, December 18, 2019	Jan-20
Wednesday, December 18, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Thursday, December 19, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Friday, December 20, 2019	Jan-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20
Sunday, January 26, 2020	Feb-20

[DataInsights · Accepted solution]

@Anonymous, try the following solution.

Create the data model below (joined fields have the same name in each table):

Table PayrollPosting sample data (Index column is for sorting):

Table PayrollPostingDetail sample data:

Create measures:

Total Hours Worked = SUM ( HoursWorked[Hours] )

Total Hours Budgeted = SUM ( HoursBudgeted[Hours] )

Create table visual using the following fields:

Employee[Employee]

PayrollPosting[Payroll Posting Month]

[Total Hours Worked] (measure)

[Total Hours Budgeted] (measure)