Historical analysis and working with SCD type 2.

[Anonymous]

Hi All

I have an employee table which is SCD type 2, all record are overwritten when updated and I just have an change table that could track before and after values. In my data mode, I created a relationship between employee table and change table via employee id and here is an example of my tables;

Employee table:

Change Table:

I have added a dynamic slicer to my canvas and I want to calculate the number of full-time employees based on slicer date (for example: as at end of december 2022).

I wrote below dax to calculate number of the active employees as at end of slicer date;

ActiveEmployees=
COUNTROWS(
   FILTER (
        Employee,
        (Employee[StartDate] <= ENDOFMONTH(Dates[Date])
                    && Employee[TerminationDate] == BLANK () 
                       || 
                       Employee[commencementDate] <= ENDOFMONTH(Dates[Date])
                          && Employee[TerminationDate] > ENDOFMONTH(Dates[Date])
    ))

Now I want to calculate number of full-time employees, what's the best way to do this to incorporate the changes to status as per changes table?

Thanks in advance.

[amitchandak]

@Anonymous ,

ActiveEmployees=
COUNTROWS(
FILTER (
Employee,
(Employee[StartDate] <= max(Dates[Date])
&& Employee[TerminationDate] == BLANK ()
||
Employee[commencementDate] <= max(Dates[Date])
&& Employee[TerminationDate] > mx(Dates[Date])
) && Employee[Status] = "FULL" ))

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi @amitchandak 

Active employees dax works fine, I just need some ideas about the measure to bring in changes and caluculate the accurate number of full-time emloyees. for example to take into account employees that are currently full-time but they weren't full time back at the end of december 2022.