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.
I would like to calculate staff turnover rate in DAX, but the formula we are using is slightly different to the cases I've seen online:
Leavers in given period of time
divided by
Average headcount in same period of time
It is the average headcount I am struggling with: most examples take headcount at end of period, or the average of headcount at start and end, but not the actual average per day headcount in a period.
I can do this in Power Query but we need end users to dynamically interact with the data - filter by Department, date ranges, etc.
The data is as you'd expect - a fact table of employee IDs with from and to dates plus a few details.
Dummy data including (currently botched attempts at) measures:
https://1drv.ms/u/s!AmzInrHWVr2zgYU0akVhd-ilLdCp-A
thanks for any help !
Solved! Go to Solution.
Thank you both for your ideas.
I am fairly sure I have cracked it with these three measures (the names are with the real data set, not the dummy one):
Hi @TomTomTom ,
I'm glad that you have solved the issue by yourself and share it with us. Would you please consider Accept helpful reply as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Thank you both for your ideas.
I am fairly sure I have cracked it with these three measures (the names are with the real data set, not the dummy one):
@TomTomTom , At the first look at data, seems like it this blog should help
https://www.youtube.com/watch?v=e6Y-l_JtCq4
Please try these two measure expressions. Use the 2nd one in your visual with Year and Month columns.
Turnover =
VAR MaxDate =
MAX ( Calendar[Date] )
VAR MinDate =
MIN ( Calendar[Date] )
VAR vCurrent =
CALCULATE (
COUNTROWS ( Employee_Fact ),
ALL ( 'Calendar'[Date] ),
Employee_Fact[First_Day] <= MaxDate,
Employee_Fact[Last_Day] >= MinDate
)
VAR vCurrentEmployees =
DISTINCT ( Employee_Fact[Employee_ID] )
VAR vPrevMonthEmployees =
CALCULATETABLE (
DISTINCT ( Employee_Fact[Employee_ID] ),
PREVIOUSMONTH ( 'Calendar'[Date] )
)
VAR vLeavers =
COUNTROWS (
EXCEPT (
vPrevMonthEmployees,
vCurrentEmployees
)
)
RETURN
DIVIDE (
vLeavers,
vCurrent
)
Avg Monthly Turnover =
AVERAGEX (
SUMMARIZE (
'Calendar',
'Calendar'[Year],
'Calendar'[Month]
),
[Turnover]
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks Pat.
Possibly I am missing something, but I don't think this gives me average employees in a given period.
For instance, if we employed:
Day 1 - 1 employee (doesn't leave)
Day 2 - 997 employees (996 all leave)
Day 3 - 1 employee (same as day 1 employee)
then our average employee in this period is 333 and so our turnover is 996/333.
Or have I misunderstood?
thanks,
Tom
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
91 | |
89 | |
79 | |
69 | |
68 |
User | Count |
---|---|
222 | |
128 | |
117 | |
82 | |
77 |