DAX | Annualized Attrition Calculation per Month and by Year

[ExodusB]

Annualized Attrition Calculation per Month and by Year

Monday

Need help! I am stuck with my Annualized formula and cannot return the correct number of months completed for each year. 

My Formula for Annualized Attrition should be (Total Attrition / Headcount) / Number of Months completed *Total Number of Months in a year, therefore I am needing help to get the Annualized Attrition values same as below:

However, my formula counts all the number of months in my data regardless of which year. I need to have a separate month count for 2024 and 2025 (possibly even for years 2022 until 2023)

[FarhanJeelani]

Hi @ExodusB ,

Your issue is that your DAX formula for Annualized Attrition is not correctly calculating the Number of Months Completed per year. Instead, it is counting all months in the dataset.

Try below:
Modify your Number of Months Completed calculation to count only distinct months per year.

Step 1: Create a measure for counting distinct months per year
DAX

Months_Completed =
VAR CurrentYear = SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Month]),
'Table'[Year] = CurrentYear
)

Step 2: Update your Annualized Attrition Measure
DAX

Annualized_Attrition =
VAR TotalAttrition = SUM('Table'[Total Attrition])
VAR AvgHeadcount = AVERAGE('Table'[Average Head Count])
VAR MonthsCompleted = [Months_Completed]
VAR TotalMonths = 12

RETURN
IF(
MonthsCompleted > 0,
(TotalAttrition / AvgHeadcount) / MonthsCompleted * TotalMonths,
BLANK()
)

Please mark this post as  solution if it helps you. Appreciate Kudos.

[ExodusB]

Hi @FarhanJeelani your response is truly appreciated.

I don't have a PowerBI model yet and am still working this in Excel query so some functions such as SELECTEDVALUE isn't working. Is the MAX function an alternative function or would you recommend me  reposting this to Power Query forum?

[FarhanJeelani · Accepted solution]

Hi @ExodusB ,

Yes, since you're working in Excel (likely using Power Query or Pivot Tables), some DAX functions like SELECTEDVALUE won't work. However, you can replace it with MAX, VALUES, or FILTER depending on your setup.

Alternative approach using Excel formulas or Power Query:
Option 1: Using Power Pivot (DAX in Excel)
If you're using Power Pivot in Excel, you can modify the DAX measure:

Months_Completed =
VAR CurrentYear = MAX('Table'[Year])
RETURN
CALCULATE(
DISTINCTCOUNT('Table'[Month]),
'Table'[Year] = CurrentYear
)

This replaces SELECTEDVALUE with MAX, which works well when used in a Pivot Table context.

Option 2: Using Power Query (M Language)
If you're working in Power Query, you can calculate the distinct month count per year using Group By:

Go to Power Query Editor

Select the Year column

Click Group By → Set it to count distinct Month values per Year

Merge this back into your main table

Option 3: Using Excel Formulas
If your data is in Excel without Power Query, you can use SUMIFS to count distinct months per year:

=SUM(IF(FREQUENCY(IF(A2:A100=YearCell, B2:B100), B2:B100) > 0, 1))

A2:A100 = Year column

B2:B100 = Month column

YearCell = Reference to the year you are checking

Please mark this post as solution if it helps you. Appreciate Kudos.

[ExodusB]

Hi @FarhanJeelani 

This is how it returns the desired value:

Months_completed should return as

December = 12

January = 1

February = 2

2024 = 12

2025 = 2