Forum Discussion
Turnover calculation in DAX
I'm trying to do a trend line for turnover, where the calculation is Total leavers in the week / Average weekly headcount in the preceeding 52 weeks. I have managed to make it work using a fixed ...
Hi Anonymous ,
According to your sample tables, would you please refer to the measure below:
Turnover = VAR a = COUNTROWS ( leavers ) VAR b = CALCULATE ( COUNTROWS ( employees ), FILTER ( ALL ( employees ), RELATED ( weeklist[Offset to Current week] ) >= MAX ( weeklist[Offset to Current week] ) - 52 && RELATED ( weeklist[Offset to Current week] ) < MAX ( weeklist[Offset to Current week] ) ) ) / 52 RETURN DIVIDE ( a, b )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
amitchandak the sample data was just to give an indication of the layout, obviously to do a rolling 52 weeks for the last 52 weeks trend, 104 weeks history would be required. Shall I upload sample data? What's the best way to do that? A PBIX file?
v-deddai1-msft
Community Support
Community SupportHi Anonymous ,
According to your sample tables, would you please refer to the measure below:
Turnover =
VAR a =
COUNTROWS ( leavers )
VAR b =
CALCULATE (
COUNTROWS ( employees ),
FILTER (
ALL ( employees ),
RELATED ( weeklist[Offset to Current week] )
>= MAX ( weeklist[Offset to Current week] ) - 52
&& RELATED ( weeklist[Offset to Current week] )
< MAX ( weeklist[Offset to Current week] )
)
) / 52
RETURN
DIVIDE ( a, b )
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