Need help: Calculating Beginning Headcount to use for Turnover

[Anonymous]

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 !! 🙂

[Anonymous]

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]

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.