cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## Calculate HR turnover and show trend

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?

1 ACCEPTED SOLUTION
Employee

Hi Erhodes,

Yes, you can use this way to count the headcount for each month.

Regards,

Charlie Liao

14 REPLIES 14
Frequent Visitor

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
Not applicable

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
Not applicable

Hi

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

Thanks

Loren

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.

Employee

Hi Erhodes,

Yes, you can use this way to count the headcount for each month.

Regards,

Charlie Liao

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.

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

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

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.

New Member

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!

Regular Visitor

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?

New Member

Hello,

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

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors