Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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 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)

WeekOffset to Current week
2020Wk01-15
2020Wk02-14

 

leavers (lists leavers and the week they left):

Employee NoWeek left (Joined to Week column of weeklist table)
1012020Wk02
1022020Wk04

 

employees (lists all active employees for every week):

EmployeeWeek (Joined to Week column of weeklist table)Note
1012020Wk01 
1022020Wk01 
1032020Wk01 
1022020Wk02 
1032020Wk02 
1042020Wk03New Starter
1032020Wk04 
1042020Wk04 

 

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.

1 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

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

 

Any questions and bugs - please let me know.

 

Best

D

amitchandak
Super User
Super User
Anonymous
Not applicable

@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.

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

Anonymous
Not applicable

@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?

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 , after removing sensetive information load it on one drive or dropbox and share the link here or PM me

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.