cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Regular Visitor

## 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?

1 ACCEPTED SOLUTION
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!
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!

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors