cancel
Showing results for
Did you mean:
Highlighted
New Member

## Calculate Employee Turnover Rate per month

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Calculate Employee Turnover Rate per month

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

return
Terminates_during_the_period/Employess_at_the_beginning_of_the_period

Result:

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.

4 REPLIES 4
Highlighted
Super User IX

## Re: Calculate Employee Turnover Rate per month

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
New Member

## Re: Calculate the employee turnover rate per month

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

Highlighted
Community Support

## Re: Calculate Employee Turnover Rate per month

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

return
Terminates_during_the_period/Employess_at_the_beginning_of_the_period

Result:

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.

Highlighted
Community Support

## Re: Calculate Employee Turnover Rate per month

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

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021