In one of my data sources I have a payroll table. For each employee it has a row for each date that they worked. I'd like to build something for future booked work. I think I have all the components to build it but I'm not sure how to put it together. I believe it's something to do with the table formulas that I haven't quite managed to wrap my brain around yet like SUMMARIZE and CALCULATETABLE and so forth. Perhaps one of you can show me the way.
Here's the plan: I don't care about which actual days of the week people will work. I just want them marked on all weeks in the range of their scheduled work. So where the payroll shows a row for each day for each employee, and it skips dates when they didn't work, I want a row per week per employee (see below).
By the way, I know of several ways to get a count of working employees per week without creating this extra table. This table is for other purposes.
The basis for this will be two tables:
1) my standard DateTable, which includes of course a continuous [Date] column as well as a [Week] column (which is the date of the last day of the week for each [Date]), and
2) a BookedWork table, which includes [EmployeeID], [StartDate] and [EndDate].
DateTable:
Date Week
5/29/2016 6/4/2016
5/30/2016 6/4/2016
5/31/2016 6/4/2016
6/1/2016 6/4/2016
6/2/2016 6/4/2016
6/3/2016 6/4/2016
6/4/2016 6/4/2016
6/5/2016 6/11/2016
6/6/2016 6/11/2016
6/7/2016 6/11/2016
6/8/2016 6/11/2016
6/9/2016 6/11/2016
6/10/2016 6/11/2016
6/11/2016 6/11/2016
6/12/2016 6/18/2016
6/13/2016 6/18/2016
6/14/2016 6/18/2016
...etc.
BookedWork:
EmployeeID StartDate EndDate
Emp0015 5/1/2016 6/4/2016
Emp0016 5/1/2016 6/4/2016
Emp0027 6/3/2016 7/9/2016
Emp0028 6/11/2016 8/1/2016
Emp0029 6/18/2016 8/10/2016
...etc.
And this is the desired output:
WorkSchedule:
Week EmployeeID
6/4/2016 Emp0015
6/4/2016 Emp0016
6/4/2016 Emp0027
6/11/2016 Emp0027
6/11/2016 Emp0028
6/18/2016 Emp0027
6/18/2016 Emp0028
6/18/2016 Emp0029
...
Any ideas?
Did I answer your question? Mark my post as a solution!Proud to be a Super User!
