Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working on trying to capture the turnover rate but for some odd reason, i keep getting the wrong numbers especially if i select a filter. In my measure i am using the ALL constraint, but it doesnt seem to apply for "employee count at beginning" and employee count at end".
The formula i am using is
Employee who left during the period / ((employee at beginning of time period + employee at end of time period) /2)
Please can you advise what am i missing.
Employee_Code | HireDate | Termination_Date | Termination_Reason |
E101 | 8/7/2019 | 11/11/2021 | Moved |
E102 | 10/21/2019 | 6/16/2020 | Better Offer |
E103 | 2/3/2020 | 4/30/2021 | Resigned |
E104 | 2/8/2021 | null | |
E105 | 3/8/2021 | 6/8/2021 | Moved |
E106 | 8/23/2021 | 9/30/2021 | Better Offer |
E107 | 10/18/2021 | 11/8/2021 | Resigned |
E108 | 2/28/2022 | null | |
E109 | 9/15/2019 | 1/1/2021 | consultant |
E110 | 1/1/2017 | null | |
E111 | 11/1/2017 | 3/24/2021 | resignation |
E112 | 6/29/2020 | null | |
E113 | 1/1/2017 | 9/22/2021 | Covid vaccine policy |
E114 | 8/26/2019 | null | |
E115 | 8/27/2012 | 12/24/2013 | Moved |
E116 | 8/27/2002 | 2/12/2013 | Better Offer |
E117 | 8/27/2012 | 1/1/2013 | Resigned |
E118 | 8/27/2012 | 1/31/2017 | |
E119 | 8/27/2012 | 4/18/2014 | |
E120 | 9/4/2012 | 2/18/2013 | |
E121 | 8/27/2012 | 1/1/2013 | |
E122 | 9/4/2012 | 7/5/2013 | |
E123 | 9/4/2012 | 7/16/2013 |
@ChrisMendoza - thank you for the solution. i am trying that out, but I noticed that if i have a slicer on the termination reason, the "beginning" and "end counts" are way off, inspite of using the ALL function. Do you notice the same behavior too ? How to resolve such an issue.
@PBI5851 - I really wanted to get this working once I started tinkering with it. I took a look at Power BI: Employee turnover rate template - Finance BI (finance-bi.com) but I don't think I ended with the correct results. Here's what I managed to work through; hopefully you might get another idea on how to tackle this:
Employee Count =
VAR __SelectedDate = MAX('Date Table'[Date])
RETURN
SUMX(
EmpTable,
VAR __startDate = [HireDate]
VAR __termDate = EmpTable[Termination_Date]
RETURN
IF(
__startDate <= __SelectedDate &&
OR(__termDate >= __SelectedDate, __termDate = BLANK()),
1,
0
)
)
Employee Count Running Total =
VAR __maxDate = MAX('Date Table'[Date])
RETURN
CALCULATE(
[Employee Count],
'Date Table'[Date] <= __maxDate,
ALL('Date Table')
)
Employees who left =
IF(
NOT(ISBLANK([Employee Count Running Total])),
CALCULATE(
[Total Count],
USERELATIONSHIP(EmpTable[Termination_Date],'Date Table'[Date])
)
)
Turnover = DIVIDE([Employees who left],(([Employees at the beginning] + [Employee Count Running Total]) / 2))
Proud to be a Super User!
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |