Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
@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
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.
User | Count |
---|---|
83 | |
77 | |
71 | |
69 | |
54 |
User | Count |
---|---|
105 | |
100 | |
91 | |
79 | |
68 |