cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
domdom Regular Visitor
Regular Visitor

Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi there,

 

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 Smiley Happy

 

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

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi,

 

You may refer to my solution in this file.  I have not been able to compute the 12 Month Rolling Average Total Attrition

 

Hope this helps.

 

Untitled.png

12 REPLIES 12
Super User
Super User

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi,

 

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.

domdom Regular Visitor
Regular Visitor

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

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.

 

http://s000.tinyupload.com/?file_id=22930263668406270922

 

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

Super User
Super User

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi,

 

You may refer to my solution in this file.  I have not been able to compute the 12 Month Rolling Average Total Attrition

 

Hope this helps.

 

Untitled.png

domdom Regular Visitor
Regular Visitor

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi again
Thanks for your great help solving this - I've got one last issue I'm stuck with if you have some time to help?

http://community.powerbi.com/t5/Desktop/counting-date-instances-occurring-for-last-2-months-by-user/...

Thank you
karlosdsouza Regular Visitor
Regular Visitor

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

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

Super User
Super User

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi,

 

Create the relationship as shown in the image.

 

Untitled.png

karlosdsouza Regular Visitor
Regular Visitor

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

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

Super User
Super User

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

Hi,

 

Share a dataset and show the expected result.

karlosdsouza Regular Visitor
Regular Visitor

Re: Calculating rolling 12 month attrition but with dynamic slicers. Help please i'm so stuck!!

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 

 

Demonstration.png