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

[LaneLourcey]

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 Metrics	Termination 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 ID	Period	Status	... Other Metrics
123	1-31-20	Inactive
124	1-31-20	Active
125	1-31-20	Active
123	5-31-22	Inactive
124	5-31-22	Inactive
125	5-31-22	Active

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.

[LaneLourcey · Accepted solution]

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]

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

[LaneLourcey]

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.