cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Need help: Calculating Beginning Headcount to use for Turnover

Hello,

I am a beginner Power BI user and I am needing to calculate the Employee Turnover Rate.

This is how I plan to get to it:

Employees at the beginning of the period + Employees at the end of the period/2 = Average
Terminated employees/Average*100 = Turnover

The issue? I have no idea how to create a measure that calculates the number of active employees in the beginning. If there is a simpler way of coming to this solution, please feel free to suggest. I tried to create the measure EmployeeCountBegin but it's not working.

EmployeeCountBegin = CALCULATE([Active Employee Count], FILTER('Calendar 2','Calendar 2'[Date] <= MIN('Calendar 2'[Date])))

EmployeeCountEnd = CALCULATE([Active Employee Count], FILTER('Calendar 2','Calendar 2'[Date] = MAX('Calendar 2'[Date])))

Terminated Employees 3 = VAR currentDate =
MAX ( 'Calendar 2'[Date] )
RETURN
CALCULATE (
COUNTROWS ('Emp Data'),
FILTER (
'Emp Data',
(ISBLANK('Emp Data'[Job Termination Date])=FALSE() && 'Emp Data'[Job Termination Date]<=currentDate) && ('Emp Data'[Job Rehire Date]<'Emp Data'[Job Termination Date])))

Emp Data Table

 Employee ID Employee Status Company Code Hire Date Job Rehire Date Job Termination Date Job Action Job Reason Action/Reason Description Avoidable Termination Voluntary Termination TERMINATION TYPE HIRE DATE EMPLOYEE TYPE BUS ENTITY 1 Terminated OES 01/09/2017 06/28/2019 TER LAY Layoff Y N Involuntary 01/09/2017 Employee Services U.S. 2 Active OES 08/22/2016 XFR MRR .Manager Change Y N 08/22/2016 Employee Services U.S. 3 Active OES 04/10/2017 DTA CDP Correction-Department Y N 04/10/2017 Employee Services U.S. 4 Active OES 10/12/2015 DTA SUP Supervisor Change Y N 10/12/2015 Employee Services U.S. 5 Active OES 08/14/2002 DTA SUP Supervisor Change Y N 08/14/2002 Employee Services U.S. 6 Active OES 05/16/2011 DTA CDP Correction-Department Y N 05/16/2011 Employee Services U.S. 7 Active OES 04/27/2009 DTA CDP Correction-Department Y N 04/27/2009 Employee Services U.S. 8 Terminated OES 05/31/2016 07/05/2019 TER RES Resignation N Y Voluntary 05/31/2016 Employee Services U.S. 9 Terminated OES 04/24/2017 12/06/2018 DTA UDF User Defined Field Y N Unknown 04/24/2017 Employee Services U.S. 10 Active OES 01/28/2008 MGR MRA Mass Change-Auto Reassignment Y N 01/28/2008 Employee Services U.S. 11 Terminated OES 10/20/2014 10/19/2018 TER RES Resignation N Y Voluntary 10/20/2014 Employee Services U.S. 12 Active OES 10/16/2017 DTA CDP Correction-Department Y N 10/16/2017 Employee Services U.S. 13 Terminated OES 08/31/2015 06/28/2019 TER LAY Layoff Y N Involuntary 08/31/2015 Employee Services U.S. 14 Active OES 11/23/2015 DTA CDP Correction-Department Y N 11/23/2015 Employee Services U.S.

Calendar 2 Table

Calendar Table

Thank you !! 🙂

2 REPLIES 2
Anonymous
Not applicable

Mate, writing a measure is one thing. The other is to have a good definition of what it means "the number of active employees in the beginning." I'm asking because you've got more than 1 date in there, there's a Rehire Date as well and some other pieces of info. We need to know the verbal description of the number. Verbal and CLEAR.

Based on the data you have, please tell us the rules to calculate this number. Thanks.

Best

Darek

Anonymous
Not applicable

Hello,

The number of beginning employees means who was active at the beginning of the period. For example, if I am looking at the year 2019, I want to know who was active as of Jan 1st 2019. Active means they have a hire date with no termination date, or they have a rehire date that is greater than their termination date.

I hope this definition makes sense.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks