Forum Discussion
Staff Turnover Calculation
Hi everyone,
This is my mock table:
| ID | Hired_Date | Left_Date | Data_Date |
| Staff001 | 05/05/2005 | 31/07/2023 | |
| Staff002 | 06/05/2010 | 31/07/2023 | |
| Staff003 | 07/05/2015 | 31/07/2023 | |
| Staff004 | 07/05/2020 | 31/07/2023 | |
| Staff005 | 09/05/2023 | 01/07/2023 | 31/07/2023 |
| Staff006 | 10/01/2023 | 31/07/2023 | |
| Staff007 | 11/02/2023 | 31/07/2023 | |
| Staff008 | 15/03/2023 | 31/07/2023 | |
| Staff009 | 16/04/2023 | 31/07/2023 | |
| Staff010 | 18/05/2023 | 11/07/2023 | 31/07/2023 |
| Staff001 | 05/05/2005 | 31/08/2023 | |
| Staff002 | 06/05/2010 | 31/08/2023 | |
| Staff003 | 07/05/2015 | 31/08/2023 | |
| Staff004 | 07/05/2020 | 31/08/2023 | |
| Staff011 | 05/08/2023 | 31/08/2023 | |
| Staff006 | 10/01/2023 | 31/08/2023 | |
| Staff007 | 11/02/2023 | 31/08/2023 | |
| Staff008 | 15/03/2023 | 31/08/2023 | |
| Staff009 | 16/04/2023 | 07/08/2023 | 31/08/2023 |
System of this table is in the end of every month we collect data from companies this is why we have data_date column. We append these data and update our dashboard in PowerBI. I want to calculate Staff Turnover properly but couldn't make it in DAX.
The formula of Turnover = Count of people left / Average headcount
First of all i want to see our monthly turnover. I tried this and this kind of DAX formula:
COUNTA([Left_date])/
(COUNTA([ID]-COUNTA([Hired_Date]+COUNTA([ID])-COUNTA([Left_Date]))/2 *To understand monthly average with start of the month and end of the month*
Main formula is this. The problem is i couldn't filter hired_date ID and left_date columns by data_date. If the id, hire date and leave date is in the month of Data_date i want to use, this calculation will be true. I use slicer in my dashboard which is controlled by Data_date. So somehow i have to link data_date's year and month with other dates and filter them to get the correct result. Could you please help me?
The requirements aren't very clear, it would be useful to give an example of the expected answer you are after from the example data given, but assuming you want to be able to get a % of turnover depending on when the [Data_Date] is and only counting the unique Staff ID?
If so you just need to add a slicer for Data_Date, then add the following measure (Assuming your data table is called StaffData, replace with your actual data table's name.) and set it to the percentage format.Turnover =DIVIDE(Calculate(COUNTROWS(StaffData), NOT(ISBLANK(StaffData[Left_Date]))),(DISTINCTCOUNT(StaffData[ID])))You can then either add this measure to a Card Visual and it'll change in conjunction with a slicer on Data_Date, and if you want a month by month turnover then you just add the Turnover Measure and Data_Date columns into a Table visual and it'll give you the results on a monthly basis, It'll show 20% for 31st July 2023 and 11.11% for 31st August 2023, averaging at 27% for the entire list (You have 3 leavers and 11 staff members, 3 divided by 11 is 0.27
However in your question this part is unclear if you could clarify please.
Alperenkose wrote:
So somehow i have to link data_date's year and month with other dates and filter them to get the correct result. Could you please help me?
What are you trying to achieve? Can you give an example result you are expecting? Or did this cover it?
1 Reply
The requirements aren't very clear, it would be useful to give an example of the expected answer you are after from the example data given, but assuming you want to be able to get a % of turnover depending on when the [Data_Date] is and only counting the unique Staff ID?
If so you just need to add a slicer for Data_Date, then add the following measure (Assuming your data table is called StaffData, replace with your actual data table's name.) and set it to the percentage format.Turnover =DIVIDE(Calculate(COUNTROWS(StaffData), NOT(ISBLANK(StaffData[Left_Date]))),(DISTINCTCOUNT(StaffData[ID])))You can then either add this measure to a Card Visual and it'll change in conjunction with a slicer on Data_Date, and if you want a month by month turnover then you just add the Turnover Measure and Data_Date columns into a Table visual and it'll give you the results on a monthly basis, It'll show 20% for 31st July 2023 and 11.11% for 31st August 2023, averaging at 27% for the entire list (You have 3 leavers and 11 staff members, 3 divided by 11 is 0.27
However in your question this part is unclear if you could clarify please.
Alperenkose wrote:
So somehow i have to link data_date's year and month with other dates and filter them to get the correct result. Could you please help me?
What are you trying to achieve? Can you give an example result you are expecting? Or did this cover it?