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 All,
Looking to calculate with DAX new hire retention in a given month based on an activity table. Issue is an employee may be hired and terminated many times (see employee 1 in example below). So I can't pivot the table to determine hire and termination dates in a given timeframe.
Calculation is (Ending Month's Continuous Headcount / Starting Month's Headcount)
Example Data below:
Employee | Activity | Date |
1 | Hire | 1/1/2022 |
2 | Hire | 1/1/2022 |
1 | Termination | 1/5/2022 |
3 | Hire | 1/31/2022 |
4 | Hire | 1/31/2022 |
3 | Termination | 2/2/2022 |
1 | Hire | 2/5/2022 |
2 | Termination | 2/5/2022 |
1 | Termination | 2/6/2022 |
1 | Hire | 2/20/2022 |
For February, Employees 2, 3, and 4 were active at the start of the month. But only Employee 4 was active at the start and end of the month. So the calculation would be:
1/3 = 33%.
Any help would be appreciated.
@jdevries192 , With help from date table joined to your date of the table
Active Employees = calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Date] <= max(Date[Date]) && [Activity] = "Hire" ))
- calculate(distinctCOUNT(EmpData[ID]) , filter(EmpData, EmpData[Date] <= max(Date[Date]) && [Activity] = "Termination" ))
You are counting all hires and terminations.
I need those who are still active at the start of February (who have been hired but not terminated). And I need those who were active at the start of February and who are still active at the end of February.
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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |