Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I need help in getting the DAX for calculating 3 types of attrition :
I have been breaking my head for a few weeks , no luck. Need help here
1. Monthly Attrition rate = Total termination for the month / average of Opening and Closing HC in the month. (Opening is closing of Previous Month)
2. YTD Attrition rate = Total terminations till date in the FY / Average of Opening and Closing balance till date in the FY.
FY starts as on 01, April every year. (Opening of april is March value of every year)
3. Projected Attrition Rate = (Actual leavers + Estimated leavers) / Average of Opening and Closing balance till date
Estimated Leavers = Sum of actual terminations / # of months pasted by in the FY (Opening of april is March value of every year)
Below is the Expected result and sample data
Sample Data
Reporting Date | Employee ID | Status | Termination Date |
3/31/2023 | 1 | Active | |
3/31/2023 | 2 | Active | |
3/31/2023 | 3 | Active | |
3/31/2023 | 4 | Active | |
3/31/2023 | 5 | Active | |
3/31/2023 | 6 | Active | |
4/30/2023 | 1 | Active | |
4/30/2023 | 2 | Active | |
4/30/2023 | 3 | Terminated | 4/15/2023 |
4/30/2023 | 4 | Active | |
4/30/2023 | 5 | Active | |
4/30/2023 | 6 | Active | |
5/31/2023 | 1 | Terminated | 5/2/2023 |
5/31/2023 | 2 | Active | |
5/31/2023 | 4 | Active | |
5/31/2023 | 5 | Active | |
5/31/2023 | 6 | Active | |
5/31/2023 | 7 | Active | |
5/31/2023 | 8 | Active | |
5/31/2023 | 9 | Active | |
6/30/2023 | 2 | Active | |
6/30/2023 | 4 | Active | |
6/30/2023 | 5 | Active | |
6/30/2023 | 6 | Terminated | 6/5/2023 |
6/30/2023 | 7 | Terminated | 6/5/2023 |
6/30/2023 | 8 | Active | |
6/30/2023 | 9 | Active | |
6/30/2023 | 10 | Active | |
6/30/2023 | 11 | Active | |
6/30/2023 | 12 | Active | |
6/30/2023 | 13 | Active | |
7/31/2023 | 2 | Active | |
7/31/2023 | 4 | Active | |
7/31/2023 | 5 | Active | |
7/31/2023 | 8 | Active | |
7/31/2023 | 9 | Active | |
7/31/2023 | 10 | Active | |
7/31/2023 | 11 | Active | |
7/31/2023 | 12 | Active | |
7/31/2023 | 13 | Active | |
8/31/2023 | 2 | Active | |
8/31/2023 | 4 | Active | |
8/31/2023 | 5 | Active | |
8/31/2023 | 8 | Active | |
8/31/2023 | 9 | Active | |
8/31/2023 | 10 | Active | |
8/31/2023 | 11 | Active | |
8/31/2023 | 12 | Active | |
8/31/2023 | 13 | Active | |
9/30/2023 | 2 | Active | |
9/30/2023 | 4 | Terminated | 9/20/2023 |
9/30/2023 | 5 | Active | |
9/30/2023 | 8 | Active | |
9/30/2023 | 9 | Active | |
9/30/2023 | 10 | Active | |
9/30/2023 | 11 | Active | |
9/30/2023 | 12 | Active | |
9/30/2023 | 13 | Active | |
10/30/2023 | 2 | Active | |
10/30/2023 | 5 | Active | |
10/30/2023 | 8 | Active | |
10/30/2023 | 9 | Active | |
10/30/2023 | 10 | Active | |
10/30/2023 | 11 | Active | |
10/30/2023 | 12 | Active | |
10/30/2023 | 13 | Active | |
11/30/2023 | 2 | Active | |
11/30/2023 | 5 | Active | |
11/30/2023 | 8 | Active | |
11/30/2023 | 9 | Active | |
11/30/2023 | 10 | Active | |
11/30/2023 | 11 | Active | |
11/30/2023 | 12 | Active | |
11/30/2023 | 13 | Active | |
12/30/2023 | 2 | Active | |
12/30/2023 | 5 | Active | |
12/30/2023 | 8 | Active | |
12/30/2023 | 9 | Active | |
12/30/2023 | 10 | Active | |
12/30/2023 | 11 | Active | |
12/30/2023 | 12 | Active | |
12/30/2023 | 13 | Active |
Solved! Go to Solution.
PBI file attached. Our answers differ (as compared to what is shown in your Google sheets file) from Sep 2023 onwards because in the PBI file, there is churn in Sep 2023.
Hope this helps.
Hi guys,
How do I create the base dataset that you have used at the start if I have an employee table with simple list of employees with start dates, status and end dates if terminated. Like below.
Name | Status | Start Date | End Date |
1 | Terminated - other | 9/16/2020 | 9/24/2024 |
2 | Terminated - left ACN | 9/24/2022 | 6/5/2024 |
3 | Active | 8/20/2018 | |
4 | Active | 9/17/2018 | |
5 | Active | 9/17/2018 | |
6 | Active | 9/17/2018 | |
7 | Active | 9/17/2018 | |
8 | Active | 9/17/2018 | |
9 | Active | 9/24/2018 | |
10 | Onboarding | 10/1/2024 | |
11 | Terminated - different ACN project | 6/4/2021 | 4/9/2024 |
12 | Terminated - planned reduction | 9/3/2019 | 6/4/2024 |
13 | Active | 12/17/2018 | |
14 | Active | 10/1/2020 | |
15 | Terminated - AEP requested | 9/9/2021 | 6/12/2024 |
16 | Active | 11/12/2018 | |
17 | Active | 11/26/2018 | |
18 | Active | 11/26/2018 | |
19 | Active | 11/26/2018 | |
20 | Active | 12/17/2018 | |
21 | Terminated - other | 1/17/2022 | 9/19/2024 |
22 | Active | 9/24/2018 | |
23 | Active | 11/26/2018 | |
24 | Terminated - left ACN | 5/21/2023 | 1/8/2024 |
25 | Active | 9/17/2018 | |
26 | Active | 11/12/2018 |
thanks,
Nikhar
Hi,
What is your expected result? Give a proper explanation.
Hi Ashish,
I am trying to get to the similar calculations as you had provided before. Attrition Rate%; Attrition Rate YTD; etc. My challenge is that my starting point is different. Instead of the monthly reporting data, I have an employee table with thier starting dates and ending dates. Is there someway for me to transform my data to I get the same starting point as the original post? or is it possible for me to calculate attrition rate directly from my data.
thanks,
Nikhar
Hi,
To create one row for each month between start date and end date, refer to the solution in this article - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...
To calculate the three types of attrition rates (Monthly, YTD, and Projected) in Power BI using DAX, you can follow these steps:
1. **Monthly Attrition Rate**:
```DAX
Monthly Attrition Rate =
VAR PreviousMonthHC = CALCULATE(SUM('Table'[Status] = "Active"), PREVIOUSMONTH('Table'[Reporting Date]))
VAR CurrentMonthTerminations = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Terminated")
RETURN
DIVIDE(CurrentMonthTerminations, (PreviousMonthHC + SUM('Table'[Status] = "Active")) / 2, 0)
```
2. **YTD Attrition Rate**:
```DAX
YTD Attrition Rate =
VAR FYStartDate = DATE(YEAR(MIN('Table'[Reporting Date])) + IF(MONTH(MIN('Table'[Reporting Date])) >= 4, 0, -1), 4, 1)
VAR CurrentFYTerminations = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Terminated", 'Table'[Reporting Date] >= FYStartDate)
VAR OpeningBalance = CALCULATE(SUM('Table'[Status] = "Active"), 'Table'[Reporting Date] = EOMONTH(FYStartDate, -1))
VAR ClosingBalance = CALCULATE(SUM('Table'[Status] = "Active"), 'Table'[Reporting Date] = LASTDATE('Table'[Reporting Date]))
RETURN
DIVIDE(CurrentFYTerminations, (OpeningBalance + ClosingBalance) / 2, 0)
```
3. **Projected Attrition Rate**:
```DAX
Projected Attrition Rate =
VAR CurrentFYTerminations = CALCULATE(COUNTROWS('Table'), 'Table'[Status] = "Terminated")
VAR NumberOfMonthsPassed = DATEDIFF(MIN('Table'[Reporting Date]), TODAY(), MONTH)
VAR OpeningBalance = CALCULATE(SUM('Table'[Status] = "Active"), 'Table'[Reporting Date] = EOMONTH(FYStartDate, -1))
VAR ClosingBalance = CALCULATE(SUM('Table'[Status] = "Active"), 'Table'[Reporting Date] = LASTDATE('Table'[Reporting Date]))
RETURN
DIVIDE(CurrentFYTerminations + CurrentFYTerminations / NumberOfMonthsPassed, (OpeningBalance + ClosingBalance) / 2, 0)
```
Replace `'Table'` with the actual name of your table. These measures assume you have a table named `'Table'` containing the sample data provided.
Once you've created these measures, you can add them to your Power BI report and use them in visualizations to display the attrition rates. Make sure to adjust the calculations and filters as needed based on your specific data model and requirements.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!
This doenst work because the use of the Sum function is wrong.
Expected results:
Monthly Attrition
Month | Headcount | Terminations | Opening Headcount | Closing Headcount | Attrition % |
March | 6 | 0 | |||
April | 5 | 1 | 6 | 5 | 18.18% |
May | 7 | 1 | 5 | 7 | 16.67% |
June | 9 | 2 | 7 | 9 | 25.00% |
July | 9 | 0 | 9 | 9 | 0.00% |
August | 9 | 0 | 9 | 9 | 0.00% |
September | 8 | 1 | 9 | 8 | 11.76% |
October | 8 | 0 | 8 | 8 | 0.00% |
November | 8 | 0 | 8 | 8 | 0.00% |
December | 8 | 0 | 8 | 8 | 0.00% |
Projected Attrition result :
Month | Headcount | Terminations | Opening Headcount | Closing Headcount | average Headcount | Estimated Leavers | Attrition % |
April | 5 | 1 | 6 | 5 | 5.5 | 1 | 18.18% |
May | 7 | 1 | 5 | 7 | 6.5 | 1 | 30.77% |
June | 9 | 2 | 7 | 9 | 7.5 | 1 | 44.44% |
July | 9 | 0 | 9 | 9 | 7.5 | 1 | 57.78% |
August | 1 | 68.44% | |||||
September | 1 | 77.33% | |||||
October | 1 | 84.95% | |||||
November | 1 | 91.62% | |||||
December | 0 | 97.54% |
YTD Attrition expected result:
Month | Headcount | Terminations | Opening Headcount | Closing Headcount | average Headcount | Attrition % |
April | 5 | 1 | 6 | 5 | 5.5 | 18.18% |
May | 7 | 1 | 5 | 7 | 6.5 | 30.77% |
June | 9 | 2 | 7 | 9 | 7.5 | 53.33% |
July | 9 | 0 | 9 | 9 | 7.5 | 53.33% |
August | 9 | 0 | 9 | 9 | 7.5 | 53.33% |
September | 8 | 1 | 9 | 8 | 7 | 71.43% |
October | 8 | 0 | 8 | 8 | 7 | 71.43% |
November | 8 | 0 | 8 | 8 | 7 | 71.43% |
December | 8 | 0 | 8 | 8 | 7 | 71.43% |
Hi Ashish,
Thankyou very much for your response. This did help a lot.
Very grateful to you 🙂
Explaining the project attrition calculation again :
Considering we are now in Aug 2023:
- April - Jul will have the YTD calculation based on actuals. Aug - March will have projected Attrition.
Projected Attrition = (Actual leavers + Estimated leavers) / Average of Opening and Closing balance till date
Estimated Leavers Aug = Sum of actual leavers till date / # of months passed by in the FY+1
Estimated leavers for Aug = (Sum of leavers from Apr - Jul) / 5
5 = No of months passed by in the FY (April, May, June, Jul+ Aug)
Estimated leavers for Sep = (Sum of leavers from Apr - Jul) / 6
6 = No of months passed by in the FY (April, May, June, Jul, Aug +Sep)
Estimated leavers for Oct = (Sum of leavers from Apr - Jul) / 7
7 = No of months passed by in the FY (April, May, June, Jul, Aug, Sep+ Oct)
Below is the expected results table:
Please help on this and let me know if you have any questions.
Very g rateful to you! 🙂
FY Year | Year | Month | Headcount | Terminations | Opening Headcount | Closing Headcount | average Headcount | Estimated Leavers | Attrition % |
FY23 | 2023 | April | 5 | 1 | 6 | 5 | 5.5 | ||
FY23 | 2023 | May | 7 | 1 | 5 | 7 | 6.5 | ||
FY23 | 2023 | June | 9 | 2 | 7 | 9 | 7.5 | ||
FY23 | 2023 | July | 9 | 0 | 9 | 9 | 7.5 | ||
FY23 | 2023 | August | 1 | 68.44% | |||||
FY23 | 2023 | September | 1 | 77.33% | |||||
FY23 | 2023 | October | 1 | 84.95% | |||||
FY23 | 2023 | November | 1 | 91.62% | |||||
FY23 | 2023 | December | 0 | 97.54% |
You are welcome. It is difficult to visualise your requirement because April (the month in which we are), is the first month of the FY and therefore all months of this FY should be estimated calculations.
Anyways, share the download link of an Excel worksheet and in there, via formulas, show the calculation of estimated leavers and estimated attrition % (the last 2 columns which you have shown in your table).
PBI file attached. Our answers differ (as compared to what is shown in your Google sheets file) from Sep 2023 onwards because in the PBI file, there is churn in Sep 2023.
Hope this helps.
Thankyou so much for help!
You are truely Genius 🙂 This also helped me to understand the way we could breakdown the DAX to get a solution.
You are welcome. Thank you for your kind words.
Access denied message.
@Megha2498 I can't get your numbers for Mothly Attrition rate. 1 leaver and 5 headcount at the end of the month gives me 1/5 or 20% ??
Monthly Attrition =
VAR __Count = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [Status] = "Active"), "__ID", [Employee ID])))
VAR __Term = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table', [Status] = "Terminated"), "__ID", [Employee ID])))
VAR __Result = DIVIDE( __Term, __Count, 0 ) + 0
RETURN
__Result
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
72 | |
49 |
User | Count |
---|---|
142 | |
139 | |
110 | |
69 | |
55 |