Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Please find the full solution attached. Handles the future and the past correctly.

 

Any questions and bugs - please let me know.

 

Best

D