Active Employees per Month
Hi,
I'm encountering an issue when attempting to calculate active employees per month over the current year.
We have a dimEmployee table that hosts a StartDate and EndDate column. An active employee per month is defined by StartDate <= LastDayOfMonth and EndDate > LastDayOfMonth.
The below graph is an attempt to use an "active" column which is defined by StartDate <= CurrentDate and EndDate > CurrentDate. This is an incorrect trend since we're attempting to find the headcount per month. We need the "CurrentDate" to be calculated as the last day of the month being calculated.
Can anyone help solve the issue without cross applying the the dimEmployee table and the fact table to create a new fact for every active contractor based on month?
Thanks,
Steven
-
Hi Steven,
this looks to me as data modelling thing, you need to get your data (through Transpose) to stage where table holds all possible dates and users combinations as lines (within date ranges specified). Soemthing liek that:
user id | date
1 | 1/1/2019
1| 1/2/2019
1| 1/3/2019
and so on until you get all dates within start and end date. Then on front end you count user id, with filter setting it to last day of month. Hope that helps.
Regards,
Michael
Please sign in to leave a comment.
Comments
2 comments