Calculate Employee Turnover Rate per month

[Anonymous]

Hi, everyone. I'm new here and I need your help:

I need you to help me implement a DAX formula to calculate the monthly Turnover.

I have a consolidated database of Monthly Closing Reports that refer to all employee information by month.

The important columns that this table contains for this calculation are:

- Date Report: This column obviously indicates the month of the report. It contains only the first day for each month. For example In this format: Jan-01-2020
- Join Date: It is the date the employee joined the company.
- Active Headcount: If in the month of the report the employee ends up with "Active" status, this is represented by a "1" in this column. This is the column that sum if I want to display the headcount of the month.
- Exit Date: This is the worker's exit date.
- Exit Headcount: If in the reporting month the worker contains an exit date, his headcount is counted in this column. This column is to know the sum of the total departures in the period.

This is how the table looks:

The formula that I want to use to calculate the monthly TurnOver is:

Turnover Rate = # Terminates during the period / # Employees at the beginning of the period.

Where:

• #Terminates during the period: is the sum of the outputs for the month, in this case, the "Exit Headcount" column.
• #Employess at the beginning of the period: in this case, the sum of the headcount of the previous month will be the initial headcount for the following month. The column to take into account is "Active Headcount" (Remember that this monthly headcount is the one that ends in the month, therefore this will be the initial one for the month). For example, what I sum in this column in the month of January, will be the initial for the month of February.

I hope you can help me as soon as possible.

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi  @Anonymous

Due to I don't know your data model, I build a table like the screenshots.

From your calculate logic I think 

Terminates_during_the_period is the sum of Exit Headcount in this month.

Employess_at_the_beginning_of_the_period is the sum of Active Headcount in piror month.

And we divide them to get the percent of Resign.

I add a Month column and an Index column to get the result.

Index Column:

Index = RANKX('Table','Table'[Date Report],,ASC,Dense)

Column:

Column =
var Terminates_during_the_period = CALCULATE(SUM('Table'[Exit Headcount]),ALLEXCEPT('Table','Table'[Year Report],'Table'[Month]))
var Employess_at_the_beginning_of_the_period = CALCULATE(SUM('Table'[Active Headcount]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))

return
Terminates_during_the_period/Employess_at_the_beginning_of_the_period

Result:

If this reply still couldn't help you to solve your problem, please provide me with more details about the result you want, or give me a screen shot of the result. Or you can share your pbix file with me by your OneDrive for Business.

You can download the pbix file from this link: Calculate Employee Turnover Rate per month

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Greg_Deckler]

@Anonymous Can you post that data as text in a table, this is a pretty common request. It is actually Recipe 1 of Chapter 7 of my book DAX Cookbook, you can get the DAX code here: https://github.com/gdeckler/DAXCookbook

If you provide sample data I can mock up something in a PBIX file just for your situation.

[Anonymous]

Hello @Greg_Deckler, Thanks for your help.
I have sent you a private message with the data, so you can simulate something similar.