cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

Reyesnes_0-1599774506664.png

 

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
Community Support

Re: Calculate Employee Turnover Rate per month

Hi  @Reyesnes

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

1.png

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:

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

View solution in original post

4 REPLIES 4
Highlighted
Super User IV
Super User IV

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.


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

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
Community Support

Re: Calculate Employee Turnover Rate per month

Hi  @Reyesnes

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

1.png

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:

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

View solution in original post

Highlighted
Community Support
Community Support

Re: Calculate Employee Turnover Rate per month

Hi @Reyesnes 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors