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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LaneLourcey
Frequent Visitor

Calculate Turnover Rate with table of all terminations & table with monthly headcount snapshots

Hey everyone, I'm trying to caluclate turnover using a different dataset than I've seen anywhere else.

 

Basicly, I've got a list of terminations shaped like the following...

Emp ID... Other MetricsTermination Date
123...1-1-20
124...5-17-22

And I also have a table that is a monthly headcount report (containing currently active and all inactive) employees

Employee IDPeriodStatus... Other Metrics
1231-31-20Inactive 
1241-31-20Active 
1251-31-20Active 
1235-31-22Inactive 
1245-31-22Inactive 
1255-31-22Active 

 

I'd like to calculate the turnover for a given month(terminated employees / number of active employees), but also see that number anualized depending on the period I'm viewing (Individual month, quarter, year).

It would be much simpler to calculate turnover if I were using start date / end date of the current headcount, but our organization rehires & grows/shrinks too much for this to be a good way to look at the turnover in a unbiased way.

1 ACCEPTED SOLUTION
LaneLourcey
Frequent Visitor

My solution ended up being to get a report that was a list of all of the seperations, and then to create a field that calculated the last date of the month so I could use a common date period when comparing with the monthly headcount.

= Table.TransformColumns(#"Start Set Time Period",{{"Last Working Date - Copy", Date.EndOfMonth, type date}})

 

Then I added a column called Employment Status, and set it to withdrawn for the seperations list and active for the monthly headcount, and appended the two queries into one called Turnover.

 

Finally I calculated the turnover rate with this measure, and it's fully filterable based on location, job type, ect.

 

Turnover = 
VAR ActiveEmps = CALCULATE(COUNTA('Turnover'[8 Digit ID]), ALLSELECTED('Turnover'[Period]), 'Turnover'[Employment Status] = "Active")
VAR Terms = CALCULATE(COUNTA('Turnover'[8 Digit ID]), ALLSELECTED('Turnover'[Period]), 'Turnover'[Employment Status] = "Withdrawn")
Return (Terms / ActiveEmps) * 12

View solution in original post

3 REPLIES 3
LaneLourcey
Frequent Visitor

My solution ended up being to get a report that was a list of all of the seperations, and then to create a field that calculated the last date of the month so I could use a common date period when comparing with the monthly headcount.

= Table.TransformColumns(#"Start Set Time Period",{{"Last Working Date - Copy", Date.EndOfMonth, type date}})

 

Then I added a column called Employment Status, and set it to withdrawn for the seperations list and active for the monthly headcount, and appended the two queries into one called Turnover.

 

Finally I calculated the turnover rate with this measure, and it's fully filterable based on location, job type, ect.

 

Turnover = 
VAR ActiveEmps = CALCULATE(COUNTA('Turnover'[8 Digit ID]), ALLSELECTED('Turnover'[Period]), 'Turnover'[Employment Status] = "Active")
VAR Terms = CALCULATE(COUNTA('Turnover'[8 Digit ID]), ALLSELECTED('Turnover'[Period]), 'Turnover'[Employment Status] = "Withdrawn")
Return (Terms / ActiveEmps) * 12
Greg_Deckler
Super User
Super User

@LaneLourcey Recipe 1 from Chapter 7 in DAX Cookbook has an employee turnover calculation. PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com)


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

That calculation works, but it's a lot simpler than what I've looking for. I can't use that for several reasons.

1. Employment could dip in the middle of the year, but new hires could still match at the end of the year.

2. I need to see the turnover per month, quarter & year - or whatever period is being looked at.

3. We have employees leave and come back, which means they have a leave date, a first working date, and a rehire date. I could remove the leave date if there is a rehire date that is present and after the leave date - but it's more accurate to look at the monthly list of employees and filter only those that are active.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.