Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am looking for a help with DAX to calculate the Employee turnover.
My data look like that:
Table name: Staff Population
To make this calculation I need 3 measuers:
1. The number of active employees at the beginning of the month
2. The number of active employees at the end of the month
3. The number of employees who left during that month
I think that I handled with the second one (like below) but still need a help with 1 and 3.
Solved! Go to Solution.
Hi @dzaba ,
You can use the following three measures:
Start month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MIN('Date'[Date])&&OR('Staff Population'[End Date]>=MIN('Date'[Date]),ISBLANK('Staff Population'[End Date]))))
end month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MAX('Date'[Date])&&OR('Staff Population'[End Date]>=MAX('Date'[Date]),ISBLANK('Staff Population'[End Date]))))
left employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[End Date] >=MIN('Date'[Date])&&'Staff Population'[End Date]<=MAX('Date'[Date])))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @dzaba ,
You can use the following three measures:
Start month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MIN('Date'[Date])&&OR('Staff Population'[End Date]>=MIN('Date'[Date]),ISBLANK('Staff Population'[End Date]))))
end month employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[Start Date]<=MAX('Date'[Date])&&OR('Staff Population'[End Date]>=MAX('Date'[Date]),ISBLANK('Staff Population'[End Date]))))
left employee = CALCULATE(COUNTROWS('Staff Population'),FILTER('Staff Population','Staff Population'[End Date] >=MIN('Date'[Date])&&'Staff Population'[End Date]<=MAX('Date'[Date])))
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
@dzaba , refer to my HR blog. Use MIn(Date[Date] for start of month nad Max(Date[Date]) for end of month
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |