Attrition DAX for HR Dashboard

[Megha2498]

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

[NikharKedia]

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

[Ashish_Mathur]

Hi,

What is your expected result?  Give a proper explanation.

[NikharKedia]

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

[Ashish_Mathur]

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

[johnbasha33]

@Megha2498 

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 !!

[NikharKedia]

This doenst work because the use of the Sum function is wrong.

[Megha2498]

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%

[Ashish_Mathur]

Hi,

PBI file attached.  Cannot understand the calculation of estimated leavers

Hope this helps.

[Megha2498]

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%

[Ashish_Mathur]

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

[Megha2498]

Sure, Here you go.

Link to excel file with the calculation and formula

Link 

[Ashish_Mathur · Accepted 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.

[Megha2498]

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. 

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[Ashish_Mathur]

Access denied message.

[Greg_Deckler]

@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