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.
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:
I hope you can help me as soon as possible.
Solved! Go to 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
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
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.
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
Hi @Anonymous
Due to I don't know your data model, I build a table like the screenshots.
From your calculate logic I think
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
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.
@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.
Hello @Greg_Deckler, Thanks for your help.
I have sent you a private message with the data, so you can simulate something similar.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |