cancel
Showing results 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

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)

 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.

1 ACCEPTED SOLUTION
Community Support

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

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

Super User

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

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

Super User

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

Community Support

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

Super User

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors