Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
domdom
Helper II
Helper II

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 🙂

 

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

16 REPLIES 16
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The file is not available, can I have the file. Thank you

 

Hi,

I do not have that file.  Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish, I'm trying to find this file, but it is removed. Please advise.

Hi,

I do not have the file now.  Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

Create the relationship as shown in the image.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

This will require using a calculated column.  The problem with using a calculated column is that it will not recalculate when a filter/slicer changes.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @Ashish_Mathur

 

No workaround for this ? for attrition this was kind of critical importance ! Would be great if you can suggest !

Hi,

 

I can only think of a calculated column formula approach.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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