cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Calculate the last 12 months of the Natural Attrition rate

Hi,

I am looking for a formula that will sum up the last 12 months of the Natural attrition % measure that I have created

Natural Attrition measure Formula =

DIVIDE(
SUM('20230301_Comms_and_Seps_Data'[hc_loss]),
SUM('20230301_Comms_and_Seps_Data'[hc_total])
)
This gives me the row by row attrition total by year and month, However I then need to add these percentages up to give me the sum of the last 12 months. This may seem odd, but this was how it was calculated using R programming.

The dataset already contains the Headcount (HC) at the end of each month, HC Loss, HC Gain - See Example of Dummy Data below:

 Year Month Total Head Count Sum of hc_loss Sum of hc_gain Natural Attrition % 2018 March 10000 200 100 2.00% 2018 April 10010 201 101 2.01% 2018 May 10011 202 102 2.02% 2018 June 10012 203 103 2.03% 2018 July 10013 204 104 2.04% 2018 August 10014 205 105 2.05% 2018 September 10015 206 106 2.06% 2018 October 10016 207 107 2.07% 2018 November 10017 208 108 2.08% 2018 December 10018 209 109 2.09% 2019 January 10019 210 110 2.10% 2019 February 10020 211 111 2.11% 2019 March 10021 212 112 2.12% 2019 April 10022 213 113 2.13% 2019 May 10023 214 114 2.14% 2019 June 10024 215 115 2.14% 2019 July 10025 216 116 2.15% 2019 August 10026 217 117 2.16% 2019 September 10027 218 118 2.17% 2019 October 10028 219 119 2.18% 2019 November 10029 220 120 2.19% 2019 December 10030 221 121 2.20%

I need to calculate the Rolling 12 month attrition rate - I think by selecting only the last HC in the previous 12 months and summing up all the HC Loss in that 12 month period (then dividing the two)

Or doing what we did in R and summing up the last 12 months of the Natural Attrition % column - Example (December 2019 back to January 2019 - Sum of the Attrition is: 25.80% in the dummy data above.

A formulas I have tried are listed below:

Rolling 12 Month Natural Attrition =
var StartDay1 = NEXTDAY(LASTDATE(DATEADD('Date Table'[Date], -12, MONTH)))
var EndDay1 = LASTDATE('Date Table'[Date])
return
CALCULATE([Natural Attrition %],DATESBETWEEN('Date Table'[Date], StartDay1, EndDay1))

Rolling 12 v2 =
CALCULATE([Natural Attrition %],
DATESINPERIOD('Date Table'[Date], MAX('Date Table'[Date]),-12,MONTH))

Natural Attrition % running total in Month =
CALCULATE(
[Natural Attrition %],
FILTER(
CALCULATETABLE(
SUMMARIZE('Date Table', 'Date Table'[Month Number], 'Date Table'[Month]),
ALLSELECTED('Date Table')
),
ISONORAFTER(
'Date Table'[Month Number], MAX('Date Table'[Month Number]), DESC,
'Date Table'[Month], MAX('Date Table'[Month]), DESC
)
)
)

ParallelPeriod = CALCULATE([Natural Attrition %], DATEADD('Date Table'[Date],12,MONTH))

Last 12 months v1 =
CALCULATE(
[Natural Attrition %],
FILTER(ALL('Date Table'[Date]),'Date Table'[Date] <= MAX('Date Table'[Date])
))

If anyone can give me some ideas, I would really appreciate it.
Thank You.

6 REPLIES 6
Regular Visitor

Hi Everyone,

Thank you for your help, I finally managed to solve the problem - Here is the DAX measure that worked:

Rolling 12 Nat Att % =
VAR v_dates = DATESINPERIOD('Date Table'[Date],MAX('Date Table'[Date]),-12,MONTH)
RETURN
SUMX(v_dates, [Natural Attrition %])
Super User

You can use the WINDOW function,

``````Natural Attrition rolling 12 months =
SUMX (
WINDOW (
-11,
REL,
0,
REL,
ALL ( 'Date'[Year month] ),
ORDERBY ( 'Date'[Year month], ASC )
),
[Natural Attrition]
)
``````

you may need to tweak the ORDERBY if your Year Month column should be sorted by a different column, e.g. Year Month Number.

Regular Visitor

Hi,

Thank you for your answer, I would love to be able to use this function, but my organisation only has the update from September 2022 and this came out in December 2022. Would you know of a workaround that I can do until the organisation updates our software?

Super User

I think this should work

``````Natural attrition rolling 12 months =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
EOMONTH ( MaxDate, -12 ) + 1
VAR SummaryTable =
CALCULATETABLE (
VALUES ( 'Date'[Year month] ),
DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
),
"@value", [Natural Attrition]
)
RETURN
SUMX ( SummaryTable, @value )
``````
Regular Visitor

Hi

Thank you for your response, however this also did not work but I did receive higher percentage results to what I have been getting previously. Still not the correct results though.

I have come to the conclusion that I need to create either a calculated column that includes the Natural Attrition % (that I can't seem to get working in Power BI) Or bring it into the data using SQL.

This is my calculated Column formula =

Natural Attrition % = DIVIDE([hc_loss],[hc_total],0)

But when I go to add it to a visualisation the sum of the column is incorrect. Would you know what I am doing wrong?

Thanks for all your help, I really appreciate it. 🙂

Super User

I think I have it working with the below 2 measures

``````Attrition Measure = SUMX(
VALUES( 'Date'[Year Month] ),
CALCULATE( DIVIDE( SUM( 'Table'[Sum of hc_loss] ), SUM( 'Table'[Total Head Count] ) ) )
)

Natural Attrition rolling 12 months =
VAR MaxDate =
MAX ( 'Date'[Date] )
VAR MinDate =
EOMONTH ( MaxDate, -12 ) + 1
VAR SummaryTable =
CALCULATETABLE (
VALUES ( 'Date'[Year month] ),
DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
),
"@value", [Attrition Measure]
)
RETURN
SUMX ( SummaryTable, [@value] )``````