Staff Turnover Calculation

[Alperenkose]

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?

[AlexanderPrime · Accepted solution]

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?