Calculate the last 12 months of the Natural Attrition rate

[AN3]

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.

[AN3]

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 %])

[johnt75]

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.

[AN3]

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?

[johnt75]

I think this should work

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

[AN3]

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

[johnt75]

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 =
    ADDCOLUMNS (
        CALCULATETABLE (
            VALUES ( 'Date'[Year month] ),
            DATESBETWEEN ( 'Date'[Date], MinDate, MaxDate )
        ),
        "@value", [Attrition Measure]
    )
RETURN
    SUMX ( SummaryTable, [@value] )