Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Alperenkose
Regular Visitor

Staff Turnover Calculation

Hi everyone,

 

This is my mock table:

IDHired_DateLeft_DateData_Date
Staff00105/05/2005 31/07/2023
Staff00206/05/2010 31/07/2023
Staff00307/05/2015 31/07/2023
Staff00407/05/2020 31/07/2023
Staff00509/05/202301/07/202331/07/2023
Staff00610/01/2023 31/07/2023
Staff00711/02/2023 31/07/2023
Staff00815/03/2023 31/07/2023
Staff00916/04/2023 31/07/2023
Staff01018/05/202311/07/202331/07/2023
Staff00105/05/2005 31/08/2023
Staff00206/05/2010 31/08/2023
Staff00307/05/2015 31/08/2023
Staff00407/05/2020 31/08/2023
Staff01105/08/2023 31/08/2023
Staff00610/01/2023 31/08/2023
Staff00711/02/2023 31/08/2023
Staff00815/03/2023 31/08/2023
Staff00916/04/202307/08/202331/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?

1 ACCEPTED SOLUTION
AlexanderPrime
Solution Supplier
Solution Supplier

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?


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

View solution in original post

1 REPLY 1
AlexanderPrime
Solution Supplier
Solution Supplier

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?


Please note - I am not affiliated with Microsoft, I'm just an end user like yourself.
Just a regular guy doin' Data Science.

If my post has helped you, please don't forget to thumbs up or click "Accept as solution" if it solved your problem!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.