Calculate HR turnover and show trend

[erhodes]

I am needing to calculate turnover within a given period and show a month trend. HR wants to caluate turnover rate as #Lost in a given period / Avg. Headcount in a given period. Calculating the avg. headcount is where I am getting tripped up. 

I have the following columns & measures which I believe are relevant:

Columns

Status: Active or Inactive

Start date

Release date: blank if status is active

Measures

Headcount: returns a count of rows where status = Active

Lost Employees: returns a count of rows where status = Inactive 

Anyone tackled this already and have suggestions?

[brethomp]

The accepted solution works gret for me, but I have an addtional challenge to add. Say that my employee data table also contained columns for Office Location and Department, And I would like to use those columns to slice/filter/color the date bassed employee counts. Is this possible?

[Anonymous]

Hi, i have a similar data.

I have first a table where i acumulate all the employees of the company (every month). Also, I have other table where I acumulate all the resignations per month.

What I need to have is a line chart where i show the cumulative rotation pero month, and when i apply a slicer (for example, If i only want to see the rotation of one of the divisions od the company), it can slice the line chart.

My formula for rotation is = Q resignation / average(Q employees month 1 + Q employees month 2)

thanks!!

[Anonymous]

Hi

Did you manage to get this to work - if so i would love to know how ?

Please help

Thanks

Loren

[Greg_Deckler]

@erhodes - Can you explain a little more about your data model and maybe post some sample data?

I am not clear on why you need the average headcount in a period versus the actual headcount in that period, your current Headcount measure. That said, you would probably have to add an ALL() clause to that to not have it filtered by the visual since employees in January that haven't left by February should be counted as active employees in both January and February for example. I'd have to play with the model a bit, interesting issue.

[erhodes]

Generally, average headcount during a period is used in calculating turnover rate as it contains the numbr of employees that started the month and the number of employees at the end of the month. I am beginning to wonder if the solution is to write measures that will calculate the starting and ending headcount balances for each month, which would entail counting rows with filters. i.e. March starting balance would be the count of all rows where hire date is before 3/1 and release date is blank. March ending would be the count of rows where hire date is before 4/1 and release date is blank or is between 3/1 and 3/31. Seems like a duct tape solution.

[v-caliao-msft · Accepted solution]

Hi Erhodes,

Yes, you can use this way to count the headcount for each month.
MonthStartHeadCount = CALCULATE(COUNT(DimEmployee[EmployeeKey]),FILTER(ALL(DimEmployee),DimEmployee[StartDate]<Year_Month[FirstDay] && ISBLANK(DimEmployee[EndDate])=TRUE()))

Regards,

Charlie Liao

[erhodes]

It almost worked. The formula calculates the starting number of employees that are currently active and have no release date. However, it does not include those that were active at one time but are no longer with the company. Ex. let's say the actual start Jan 2016 was 112; however, this formula shows 105 for Jan 2016 b/c there are 7 people that were hired before 1/1/2016, but were let go this year so "Release Date" is not blank. Someone hired on March 15, 2013 and left March 15, 2016 should be counted from April 2013 through March 2016.

[erhodes]

Thanks I'll give this try and let you know if it worked.

[robstewart89]

I'd be interested in a solution to this also.

I've managed to create a Date table then calculate no. of leavers and no. of current employees. The issue i'm having is linking this to other columns i.e. I have a column indicating which department each employee is in. I'd like to interact with the dashboard to see by department the leavers vs. average headcount and also split this by month.

The fact we have a Start date and Leaving date for each employee seems to cause barriers to achieving this though.

Robert

[erhodes]

Sounds like we have the same issue. I took the calculation provided previosuly and added a section to calculate those individuals that were employed in a given month but are no longer active. Probably a duct-tape solution, but the results came back accurate.

MonthStartHeadCount = CALCULATE(COUNT('Company Headcount'[EmployeeID]),FILTER(ALL('Company Headcount'),'Company Headcount'[Hire Date]<'Date Table'[MonthStart] && ISBLANK('Company Headcount'[Release Date])=TRUE()))+CALCULATE(COUNT('Company Headcount'[EmployeeID]),FILTER(ALL('Company Headcount'),'Company Headcount'[Hire Date]<'Date Table'[MonthStart] && 'Company Headcount'[Release Date]>'Date Table'[MonthStart]))

[rwod]

I tried to duplicated your solution here and I'm running into and issue with this piece of it... 'Date Table'[MonthStart]'

Where is that coming from, and how do I replicate that piece so that I can generate my graphic of active headcount by month?

Thanks for the help!

[jonhardy]

I was able to get the month start and month end measures up, but have been unable to find a good way to calculate an average over a period. Did you end up doing it with new columns instead of measures? If not, how did you calculate averages?

[Nikollette]

Hello, 

Can you further explain what metrics you used for MonthStart? Is this the date the hire started? 

[EZgrafton]

Your calculation works; however, how should I modify the calculation if I have five level of hierarchy and I want to be able to drill down (i.e. show the headcount by each level of hierarchy)?

Thank you.