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?
Please sign in to leave a comment.