Need help with Annualized Attrition (YTD) and Trailing 12 months (Last 12 months Running Total)

[Anonymous]

Hi Everyone,

I am calculating 2 things:

1) Annualized Attrition %

2) Trailing 12 months Attrition %

For both these i am using Monthly Attrition measure to derive formula. Monthly Attrition = No. of exits that month / Total Avg employees that month.

Actual formulas that need to be checked:

Annualized Attrition % = CALCULATE([Monthly Attrition %],DATESYTD('Date'[Date]))

TTM Attrition% = VAR CurrentDate = IF( COUNTROWS(DISTINCT(ALLSELECTED('Date'[Date]))) = 1, SELECTEDVALUE('Date'[Date]), MAX('Date'[Date]) ) VAR PreviousDate = CurrentDate - 365 VAR Result = calculate(SUMX(FILTER(ALLSELECTED('Date'[Date]),'Date'[Date]>PreviousDate && 'Date'[Date]<= CurrentDate),[Monthly Attrition %])) Return Result

Problem is, ideally both (Annualized Attrition and Trailing 12 months %) should show same values in Dec2020. But right now it is showing different values.

Can you please help me out? @Ashish_Mathur 

[TeeJay80]

Hi Ashish, I don't have formulas, that's why I turned for help here on the forum. I found a solution identical to my data, but I couldn't reach the file on One Drive. Do you have a similar template that I could use with my data? It's kind of urgent now for me as I'm trying to look for a solution for over a week.

https://docs.google.com/spreadsheets/d/11iL2TXIK8KS4tfVesQ54zykhN6Oi_3GN/edit?usp=sharing&ouid=10444...

[Ashish_Mathur]

Hi,

I can write the DAX formulas for you but i first need to understand the logic of the calculation.  Which is why i requested you to write Excel formulas in the another tab of the file to calculate attrition.  I will understand and translate those Excel formulas into measures and/or calculated columns. 

[TeeJay80]

Hi Ashish,

Please check the calculations tab. I used the formula: YTD Attrition=(Total YTD Attrition/(Average(Open Headcount total,Closed Headcount Total)

https://docs.google.com/spreadsheets/d/1Wiq3TNWXeK3qhDucFLuFAJaZ54SL4v5F/edit?usp=sharing&ouid=10444...

Let me know if this looks correct for you.

[Ashish_Mathur]

Hi,

I see circularity in the logic there.  The attrition in any year will (and rightfully should) feed into the calculation of year end headcount calculation of any year.  Since the Attrition % takes year end headcount as the denominator, there is circularity.  You do not see this because you have hard pasted all numbers in the Headcount worksheet.

[v-yuaj-msft]

Hi @Anonymous ,

Is the following result you want?

I think the first formula may be "Annualized Attrition % = CALCULATE(SUM([Monthly Attrition %]),DATESYTD('Date'[Date]))"

Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[Anonymous]

Hi V-yuaj,

Please find below excel file with the necessary details. I am stuck at Annualized Attrition for which i have mentioned the formula. I have also explained each measure if you see the header along with the DAX formula i have used.

https://drive.google.com/file/d/1w68gtfZ6tnNVL4fx7-jtz0wJiGGnMYIf/view?usp=sharing

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

I have a sample table below like this i am trying to create a view. I have used Date Table which was created using CalenderAuto. But this sample table has different values in Dec2020.

Year      Month          Monthly Attr %  Annualized Attr%   TTM Attr%

2019      December           0.05%   0.05%   0.05%

2020      January                0.70%   0.70%   0.75%

2020      February              0.42%   1.12%   1.17%

2020      March                  1.07%   2.20%   2.23%

2020      April                     0.44%   2.63%   2.67%

2020      May                     0.43%   3.05%   3.10%

2020      June                    0.45%   3.49%   3.55%

2020      July                     0.47%   3.95%   4.01%

2020      August                0.33%   4.27%   4.34%

2020      September         0.63%   4.91%   4.97%

2020      October              0.58%   5.50%   5.56%

2020      November          0.39%   5.90%   5.94%

2020      December          0.61%   6.40%   6.49%

2021      January               0.37%   0.37%   6.16%

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.  I think your annualised attrition (%) formula is incorrect.  For me to understand your working logic of the annualised attrition (%), show the monthwise calculation in a simple MS Excel file and share the download link of that Excel file as well.

[Anonymous]

Hi @Ashish_Mathur ,

Please find below excel file with the necessary details. I am stuck at Annualized Attrition for which i have mentioned the formula. I have also explained each measure if you see the header along with the DAX formula i have used.

Below is the link to the excel file including formula.

https://drive.google.com/file/d/1w68gtfZ6tnNVL4fx7-jtz0wJiGGnMYIf/view?usp=sharing

[Ashish_Mathur]

Hi,

Perhaps my requirement was not clear.

1. Please plug in numbers instead of X's
2. Had this been a simple MS Excel question, please show the Excel formulas you would have written in range I2:K15

[Anonymous]

Hi @Ashish_Mathur ,

Please find below the Excel Formulas. Let me know if anything else is required.

https://docs.google.com/spreadsheets/d/1w68gtfZ6tnNVL4fx7-jtz0wJiGGnMYIf/edit#gid=552747571

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[TeeJay80]

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

[Ashish_Mathur]

Hi,

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

[TeeJay80]

I am trying to create Yearly PowerBI attrition. I want to calculate year-to-date (assume Today: September 5th 2021) and full year Voluntary Attrition (#/%) by Organization and Location year-over-year.

Please find the dataset here: 

https://docs.google.com/spreadsheets/d/11iL2TXIK8KS4tfVesQ54zykhN6Oi_3GN/edit?usp=drive_link&ouid=10...

Following is something I would like to receive. I really appreciate for your help!

[Ashish_Mathur]

Access Denied message.  In another tab of the Excel file, please also show the desired result with formulas.  I will convert those Excel formulas into DAX formulas.