Turnover calculation in DAX

[Anonymous]

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 52 week window to the current week, but can't figure out how to make it dynamic to the week being reported for:

Turnover = DIVIDE(COUNTROWS('leavers'),CALCULATE(COUNTROWS('employees'),FILTER(ALL(weeklist),'weeklist'[Offset to Current week] >= -52)) / 52)

I have 3 tables;

weeklist (A calendar, which lists all the weeks and their offset to now)

Week	Offset to Current week
2020Wk01	-15
2020Wk02	-14

leavers (lists leavers and the week they left):

Employee No	Week left (Joined to Week column of weeklist table)
101	2020Wk02
102	2020Wk04

employees (lists all active employees for every week):

Employee	Week (Joined to Week column of weeklist table)	Note
101	2020Wk01
102	2020Wk01
103	2020Wk01
102	2020Wk02
103	2020Wk02
104	2020Wk03	New Starter
103	2020Wk04
104	2020Wk04

Obviously it's difficult to demonstrate 52 weeks history of the tables in a quick structure example, but hopefully you get the idea!

So what I'm trying to do in DAX that does the same as above, but rather than going back 52 weeks from now, it goes back 52 weeks from the week being reported for - so Reporting for week 2020Wk02 would see that it's offset by -14 from now and then return the average headcount between weeks 2019Wk03 and 2020Wk02 (the offset range is -65 to -14) and for week 2020Wk03 it would return the average between 2019Wk04 and 2020Wk03 and so on.

I'm really struggling to make this dynamic so that i can pop it into a trend chart and it will always look at the relative 52 week offset. Help would be very much appreciated.

[Anonymous]

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

Any questions and bugs - please let me know.

Best

D

[amitchandak]

Refer my blog for active/current employee : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

file :https://www.dropbox.com/s/excoqikgpyz5zvk/Current_employee.pbix?dl=0

How to deal with weeks using rank : https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

See if these can help. Does your data do not have date?

[Anonymous]

@amitchandak no dates, only the weekly offset as demonstrated, all the data is a weekly snapshot. I will see if I can repurpose what you have linked. Thanks.

[amitchandak]

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

[Anonymous]

@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 · Accepted solution]

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]

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