I've been trying to solve my issue for about a week now with no luck - so hoping someone here will be able to help and ideally provide me with a .pbix for the solution
I need to calculate monthly attrition percentages and also 12 month rolling average of company attrition by month.
Our attrition calculation is total employees who have left the company during a period divided by the average active headcount during that same period - expressed as a percentage.
Therefore monthly attrition will be total leavers during a month divided by average headcount during the month. However 12 month rolling attrition will be the total leavers during the last 12 months divided by the average active headcount during the same period. All of these calculations need to be per month.
The bit i'm struggling with is that I need to be able to cut this attrition various ways. I may want to see it by department - by gender - or by leave type (voluntary / involuntarily left)....and i'm getting nowhere with the relationships and slicers....
I have the following employee table:
Employee ID Joining Date Leaving Date Leaving Reason Gender Department
1 01/01/2017 01/01/2018 Involuntary Male Legal
2 01/05/2017 Female HR
3 20/04/2017 20/02/2018 Voluntary Male HR
4 15/04/2017 Female Legal
5 10/02/2017 04/01/2018 Voluntary Female Legal
and i'd like to end up with the following table
Year Month Monthly Attrition 12 Month Average Attrition
2017 Jan 10% 7%
2017 Feb 15% 8%
2017 Mar 5% 6%
2017 Apr 7% 7%
2017 May 1% 4%
Obviously the figures are made up and i expect you will need a larger set of data in order to work on this.
Many thanks for any help you can give would be a life saver for me
Solved! Go to Solution.
It will help if you can show the result of the sample dataset that you share so that i can verify the result of my formula with your exact % age results.
Thanks again for the help Ashish
The below link contains a excel doc with a sample dataset I have created. On the second table I show all of the attrition calculations I need in excel along with the exact table I am hoping to see in power bi. All the formulas are quite straight forward and highlighted green.
I think what's important to note is that I need to be able to filter this table using slicers - e.g if I just want to see the attriton for the HR department - I should be able to just click HR in power BI and have it update. Similarly if I just want to see it for Males - I should just click Males and have it update.
Many thanks again - I really do appreciate your help with this. Do let me know if you need anything further
I tried calculating the cummulative joinees exactly the same way as mentioned in your file. But when i display the no against the year, month it shows the cummulative joinees for entire data set e.g. if i have 70 line items in the employee data set - it shows 70 against all year month combinations. On further trouble shooting identified that relationship between DOJ column in employee table was not linked to Date in Calendar inspite of date column in calendar was made from DOJ column in employee table (Calendar = CALENDAR(MIN('Employee Table'[DOJ]),TODAY())). Where was i going wrong ? Enclosed the file for reference (without establishing relationship manually later).
@Ashish_Mathur : Now if i have to calculate the attrition % for each tenure bucket for e.g
Numerator : No of people who left in tenure of 0-1 months
Denominator : (Total people with tenure 0-1 months in begining of month - Total people with tenure 0-1 months in end of month)/2
How do i do it ?
Solution which doesnt work : Calculate the tenure in a calculated column
Why it doesnt work : For every year-month the tenure of active people will change.
Hi Ashish, the data is here. Need a solution in power BI so that denominator (HC of particular tenure) can be calculated for respective months. The month is being selected using a date filter in power BI
This is the same data you had used in the above reply