cancel
Showing results for
Did you mean:
Regular Visitor

## Dax - how to find the number of staff leavers in a certain time period

Hi all - I'm trying to work out staff turnover figures, I need to knopw the number of employees who left in a certain time period.

I have the following DAX for a headcount of staff in a period set in a table

Current Staff = CALCULATE( COUNTROWS( 'Employees' ),
FILTER( VALUES ( Employees[start_date] ), 'Employees'[start_date] <= MAX('Calendar'[Date] ) ),
FILTER( VALUES( 'Employees'[end_date] ), OR( Employees[end_date] >= MIN('Calendar'[Date] ), ISBLANK( 'Employees'[end_date] ) ) ) )

But I need to know how many staff have a leave date in the same period - I have a calendar table with inactive relationships to the data set, but just cant quite figure it out - can anyone help?

Thanks!

1 ACCEPTED SOLUTION
Resolver II

Hi @DanielAlton

Based on your sample data I created disconnected time table with coulmns like quarter, year etc.

Then I created 3 measures:

ACTIVE =
CALCULATE( DISTINCTCOUNT( Employees[Employee] ),
FILTER( VALUES( Employees[Start Date] ), Employees[Start Date] <= MAX( Dates[Date] ) ),
FILTER( VALUES( Employees[End Date] ), OR( Employees[End Date] >= MIN( Dates[Date] ), ISBLANK( Employees[End Date] ) ) ) )

This measure counts the number of unique employees in a period you choose from time table.

OUTFLUX = CALCULATE(DISTINCTCOUNT(Employees[Employee])
,Employees[End Date]>=MIN(Dates[Date])
,Employees[End Date]<=MAX(Dates[Date]))

This measure counts the employees whose contracts end in a choosen period.

ROTATION % = DIVIDE([OUTFLUX],[ACTIVE],0)

This measure counts the rotation ratio.

Here are the results based on your data:

Here you can see the test pbix file: https://filetransfer.io/data-package/tpL2wxqZ#link

5 REPLIES 5
Resolver II

Hi @DanielAlton

Based on your sample data I created disconnected time table with coulmns like quarter, year etc.

Then I created 3 measures:

ACTIVE =
CALCULATE( DISTINCTCOUNT( Employees[Employee] ),
FILTER( VALUES( Employees[Start Date] ), Employees[Start Date] <= MAX( Dates[Date] ) ),
FILTER( VALUES( Employees[End Date] ), OR( Employees[End Date] >= MIN( Dates[Date] ), ISBLANK( Employees[End Date] ) ) ) )

This measure counts the number of unique employees in a period you choose from time table.

OUTFLUX = CALCULATE(DISTINCTCOUNT(Employees[Employee])
,Employees[End Date]>=MIN(Dates[Date])
,Employees[End Date]<=MAX(Dates[Date]))

This measure counts the employees whose contracts end in a choosen period.

ROTATION % = DIVIDE([OUTFLUX],[ACTIVE],0)

This measure counts the rotation ratio.

Here are the results based on your data:

Here you can see the test pbix file: https://filetransfer.io/data-package/tpL2wxqZ#link

Regular Visitor

Thank you! By quater I mean Q1 2022 which would be January February March, Q2 2022 is April May June etc

Staff turnover is usuall done:

number of leavers / number of staff expressed as a percentage

ie 12 leavers from 240 staff is 5% turnover

Super User

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

Regular Visitor

Hi - my data is a bit like this, some employees have end dates as they have leftm some do not as they are still employed

 Employee Status Start Date End Date 000001 Dormant 27/09/2021 17/06/2022 000002 Dormant 10/05/2021 22/06/2022 000003 Dormant 11/10/2021 23/06/2022 000004 Dormant 15/02/2021 24/06/2022 000005 Dormant 03/03/2020 24/06/2022 000006 Dormant 11/04/2022 30/06/2022 000007 Dormant 19/01/2021 07/07/2022 000008 Dormant 03/09/2018 08/07/2022 000009 Dormant 23/04/2019 12/07/2022 000010 Live 24/05/2021 000011 Live 11/02/2019 000012 Live 14/02/2005 000013 Live 20/04/2022 000014 Live 01/01/2021 000015 Live 12/04/2021 000016 Live 25/03/2019 000017 Live 16/09/2020 000018 Live 07/06/2021

I'd like a table output that calculates the number of live employees each month, the number of leavers each month and then the difference between the 2 that I can change from monthly to quarterly to yearly

 Turnover by Month Headcount Leavers Staff Turnover Jan-21 100 6 6% Feb-21 105 4 4% Mar-21 101 8 8% Apr-21 98 2 2% May-21 104 4 4%

 Turnover by Quarter Headcount Leavers Staff Turnover 2021 Q1 104 18 17% 2021 Q2 97 12 12% 2021 Q3 100 11 11%

 Turnover by Year Headcount Leavers Staff Turnover 2021 105 35 33% 2022 110 22 20%
Super User

Here is the headcount part. Before continuing please confirm what you mean by Quarter, and how you calculate Staff Turnover.

Announcements