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

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try with week rank.  Is the sample data you provide is best we can use?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , after removing sensetive information load it on one drive or dropbox and share the link here or PM me

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    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