Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AN3
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:
 
YearMonthTotal Head CountSum of hc_lossSum of hc_gainNatural Attrition %
2018March100002001002.00%
2018April100102011012.01%
2018May100112021022.02%
2018June100122031032.03%
2018July100132041042.04%
2018August100142051052.05%
2018September100152061062.06%
2018October100162071072.07%
2018November100172081082.08%
2018December100182091092.09%
2019January100192101102.10%
2019February100202111112.11%
2019March100212121122.12%
2019April100222131132.13%
2019May100232141142.14%
2019June100242151152.14%
2019July100252161162.15%
2019August100262171172.16%
2019September100272181182.17%
2019October100282191192.18%
2019November100292201202.19%
2019December100302211212.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
AN3
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 %])
johnt75
Super User
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.

AN3
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?

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.