cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DanielAlton
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
Saap
Resolver II
Resolver II

Hi @DanielAlton 

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

Saap_0-1655803478011.png

Saap_1-1655803485814.png

 

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:
Saap_2-1655803615038.png

 

 

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

 

View solution in original post

5 REPLIES 5
Saap
Resolver II
Resolver II

Hi @DanielAlton 

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

Saap_0-1655803478011.png

Saap_1-1655803485814.png

 

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:
Saap_2-1655803615038.png

 

 

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

 
DanielAlton
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

lbendlin
Super User
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.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

EmployeeStatusStart DateEnd Date
000001Dormant27/09/202117/06/2022
000002Dormant10/05/202122/06/2022
000003Dormant11/10/202123/06/2022
000004Dormant15/02/202124/06/2022
000005Dormant03/03/202024/06/2022
000006Dormant11/04/202230/06/2022
000007Dormant19/01/202107/07/2022
000008Dormant03/09/201808/07/2022
000009Dormant23/04/201912/07/2022
000010Live24/05/2021 
000011Live11/02/2019 
000012Live14/02/2005 
000013Live20/04/2022 
000014Live01/01/2021 
000015Live12/04/2021 
000016Live25/03/2019 
000017Live16/09/2020 
000018Live07/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  
 HeadcountLeaversStaff Turnover
Jan-2110066%
Feb-2110544%
Mar-2110188%
Apr-219822%
May-2110444%

 

Turnover by Quarter  
 HeadcountLeaversStaff Turnover
2021 Q11041817%
2021 Q2971212%
2021 Q31001111%

 

Turnover by Year  
 HeadcountLeaversStaff Turnover
20211053533%
20221102220%

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

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors