Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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.
Hi Ashish,
Please check the calculations tab. I used the formula: YTD Attrition=(Total YTD Attrition/(Average(Open Headcount total,Closed Headcount Total)
Let me know if this looks correct for you.
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.
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
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
Hi,
Share the link from where i can download your PBI file.
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%
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.
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
Hi,
Perhaps my requirement was not clear.
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
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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:
Following is something I would like to receive. I really appreciate for your help!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |