cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 IDEmployee StatusCompany CodeHire DateJob Rehire DateJob Termination DateJob ActionJob ReasonAction/Reason DescriptionAvoidable TerminationVoluntary TerminationTERMINATION TYPEHIRE DATEEMPLOYEE TYPEBUS ENTITY
1TerminatedOES01/09/2017 06/28/2019TERLAYLayoffYNInvoluntary01/09/2017Employee Services U.S.
2ActiveOES08/22/2016  XFRMRR.Manager ChangeYN 08/22/2016Employee Services U.S.
3ActiveOES04/10/2017  DTACDPCorrection-DepartmentYN 04/10/2017Employee Services U.S.
4ActiveOES10/12/2015  DTASUPSupervisor ChangeYN 10/12/2015Employee Services U.S.
5ActiveOES08/14/2002  DTASUPSupervisor ChangeYN 08/14/2002Employee Services U.S.
6ActiveOES05/16/2011  DTACDPCorrection-DepartmentYN 05/16/2011Employee Services U.S.
7ActiveOES04/27/2009  DTACDPCorrection-DepartmentYN 04/27/2009Employee Services U.S.
8TerminatedOES05/31/2016 07/05/2019TERRESResignationNYVoluntary05/31/2016Employee Services U.S.
9TerminatedOES04/24/2017 12/06/2018DTAUDFUser Defined FieldYNUnknown04/24/2017Employee Services U.S.
10ActiveOES01/28/2008  MGRMRAMass Change-Auto ReassignmentYN 01/28/2008Employee Services U.S.
11TerminatedOES10/20/2014 10/19/2018TERRESResignationNYVoluntary10/20/2014Employee Services U.S.
12ActiveOES10/16/2017  DTACDPCorrection-DepartmentYN 10/16/2017Employee Services U.S.
13TerminatedOES08/31/2015 06/28/2019TERLAYLayoffYNInvoluntary08/31/2015Employee Services U.S.
14ActiveOES11/23/2015  DTACDPCorrection-DepartmentYN 11/23/2015Employee Services U.S.

 

Calendar 2 Table

 

Calendar TableCalendar 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,

 

Thank you for your response!

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors