Power BI: Employee turnover rate
Today I'll show you how to calculate employee turnover rate in Power BI.
Unsurprisingly, employee Turnover is a key HR metric. A high employee turnover leads to multiple costs including finding and hiring employees, onboarding them, training them, and the time spent on those tasks. A detailed article on employee turnover is available here.
This post is the continuation of an earlier post on employee count. You can read the earlier post here and download the initial Power BI file from here.
We start with an Employees table as below:
And a calculated measure for the employee count.
Employee Turnover Rate formula
Following is the Employee Turnover Rate formula (from the TalentLyft website):
The Employee Turnover Rate compares employees who left (in green in the formula) to the average number of employees (in red).
Based on the formula, a company of 10 people with 2 leaving people has a turnover rate of 20%.
Convert the Employee Turnover Rate to a Power BI formula.
To calculate the rate, we need three information:
– Number of employees who left
– Employees count at the end of the period (now)
– Employees count at the beginning of the period (12 months before)
We already have a formula for the Employees count at the end of the period, from the previous post.
Employees count 12 months before
The formula for 'employee count 12 months before' is very similar to the regular employee count formula. You can find it below:
Employee Count 12 months before =
VAR selectedDate = NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Date'[Date])))
RETURN
SUMX('Employees',
VAR employeeStartDate = [Start Date]
VAR employeeEndDate = [End Date]
RETURN IF(employeeStartDate<=selectedDate && OR(employeeEndDate>=selectedDate, employeeEndDate=BLANK() ),1,0)
)
Employees who left in the past 12 months
Now we calculate the number of employees who left in the past 12 months. To do that, we loop through the Employees table and count 1 for each employee who has left in the past 12 months.
Employees who left past 12 months =
VAR selectedDate = LASTDATE('Date'[Date])
VAR selectedDate12MonthsBefore = NEXTDAY(SAMEPERIODLASTYEAR(selectedDate))
RETURN
SUMX('Employees',
IF([End Date]>=selectedDate12MonthsBefore && [End Date]<=selectedDate, 1, 0)
)
This is what the result looks like:
Employee turnover rate in Power BI
Lastly, we calculate the employee turnover rate and divide the employees who left by the average number of employees.
Employee Turnover rate =
VAR averageEmployeesRolling12months = ([Employee Count]+[Employee Count 12 months before])/2
RETURN
[Employees who left past 12 months]/ averageEmployeesRolling12months
And now the result!
I hope this was helpful.
Enjoyed the article? Subscribe to the newsletter and download the Power BI of the article for free.
9 Comments
Many thanks pertaining to sharing the following wonderful written content on your web-site. I discovered it on google. I may check back again when you publish extra aricles.
Wonder if you have advice. I've done this above but my turnover rate percentage is going down even though the data doesn't show that:
2015- hired 81, 85 left =109% turnover
2016- hired 78, 68 left = 86% turnover
2017- hired 72, 60 left = 69% turnover
2018- hired 74, 59 left = 58% turnover
2019- hired 59, 62 left = 51% turnover
2020- hired 76, 73 left = 50% turnover
This isn't making sense to me. My calculation for turnover is:
DSP Turnover rate =
VAR averageDSPRolling12months = ([DSP Count]+[DSP count 12 months before])/2
RETURN
[DSP who left past 12 months]/ averageDSPRolling12months
I'm new to this but it doesn't make sense. Would appreciate insight. Thank you!
Hi Janelle,
I see that in 2019 62 people left and Power BI calculated a 51% turnover, while in 2018 when 59 people left (less people), the turnover was higher at 58%.
Could the reason be that the total number of people was higher in 2019 compared to 2018?
So that even if more people are leaving, percentage-wise it's lower.
Would that make sense?
If not, could you share the people at the beginning and at the end of one year? So we can check the calculation.
Regards,
Luca
Wow, this is great…
I spend months trying to get something like this. You are star!
Many thanks
Thank you Sabir!
I wish you best of luck for your future Power BI projects
What if you have positions in the table with dates as well and you want to be able to drill down into the department (using the position information)? Something like:
Start Date,End Date,Position ID,Position Start Date,Position End Date,Department,Employee ID
2018-02-03,2019-09-05,Jo01,2018-02-03,2018-08-31,Dept1,John
2018-02-03,2019-09-05,Jo02,2018-09-01,2019-09-05,Dept2,John
2018-05-07,2018-10-10,Al01,2018-05-07,2018-10-10,Dept1,Alan
2019-01-08,2019-05-09,Li01,2019-01-08,2019-05-09,Dept2,Liz
2018-05-05,,Ma01,2018-05-05,2018-12-31,Dept3,Matt
2018-05-05,,Ma02,2019-01-01,,Dept2,Matt
2019-03-02,,An01,2019-03-02,,Dept2,Ann
2019-05-07,,Sa01,2019-05-07,,Dept3,Samantha
At the top level, the turnover numbers are still the same. But now we could calculate the department turnover as well. Any thoughts on how to do that?
Also, in my case, there are status changes mixed throughout, so a person could have more than one record for a particular position with different statuses (pay changes, classifications, etc.). Any thoughts on how that would change things?
Hi David,
thank you for your message.
To reply, I would need to spend some time understanding your data and what type of charts you want to create from it.
If you wish to discuss more, you can contact me via email at lc@finance-bi.com.
Cheers,
Luca
Hi Luca, would it be possible to post the Power BI file with the turnover calculations? I am trying to follow along using the initial Power BI, and then layer on the Measures with your posted formulas, but I can't seam to get the last chart showing turnover % to match yours. Thanks for your help! What a great resource.
Hi AS,
you can download the Power BI of the article at the bottom of the article, so you can check exactly how it was built.
Let me know if that helps you,
Luca