Summarize Attrition (Turnover) by Category & Date (Excel Formula to PBI)

[funderburkj91]

Power Bi Community,

I have a report in which I am trying to implement a calculation I created in Excel, but I am lost in how to do so.

Essentially, I have a historical attrition % broken down by month and by program(client). What I would like to do is use the historical attrition % to forecast the annualized attrition for the year. The formula I have come up with takes the current year's attrition % by month if the month is over. If not, the formula substitutes last year's attrition % for the same month. The numbers are then summed up to get annualized attrition. I built this in to Excel no problem, but doing so using DAX or via another method has not come to fruition.

I have attached "dummy reports" below that include the Excel calcuations I have referenced as well as the Power BI report with the data that is available. Measures for historical attrition by program already exist, it is essentially just turning the corner to get the calculation I want for overall annual attrition and then broken down by program.

In both files, you will find even more detail as to my dilemna, what I am trying to accomplish, and on the data available.

Thank you for your help!

Josh

Excel Document w/ Calculations 

Power BI Example Report 

[DataZoe]

I got it to work, I think, but I did have to create a Date Table to extend the dates out:

Date Table = ADDCOLUMNS(CALENDAR(min('Daily Summary'[DateValue]),date(2021,1,1)),"Year",year([Date]),"Month",MONTH([Date]),"Year Month",format([Date],"mm/yyyy"),"MonthSort",format([Date],"yyyymm"))

and then I used this measure:

Annulized Att = 
    sumx(values('Date Table'[Year Month]),
        COALESCE ( 
            if(datediff(max('Date Table'[Date]),today(),MONTH)=0,blank(),[Attrition % Dummy])
        ,CALCULATE([Attrition % Dummy],dateadd('Date Table'[Date],-1,year))
        )
    )

https://github.com/DataZoe/PBIX/blob/master/Attrition%20-%20PBI%20Community%20Help%20-%20DataZoe.pbi... 

[funderburkj91]

Good work! One issue I see is that manually adding up the Annualized Att column in the furthest left matrix does not equal the subtotal's value for that column. It would seem that the subtotal is actually summing in April 2020's value as opposed to April 2019's (it should be using April 2019 since today is still in April 2020). The card is naturally off from the expected number as well since it is derived from the matrix's subtotal formula. (Manually adding up the 2020 months = 167.9, which is the expected result. Current showing is 161%.)

I also noticed that the subtotals for 2018 and 2019 in the Annualized Att columns do not match the Attrition % column even though the month by month values match between the two columns. Maybe the fix above will take care of this, but if not, something else to look at.

[DataZoe · Accepted solution]

@funderburkj91 Ah yes, need to throw in another calculate to get it to do the right thing 🙂  My co-worker had it right when he said sometimes you just have to add in another calculate haha.

Annulized Att = 
    sumx(values('Date Table'[Year Month]),
        calculate(COALESCE ( 
            if(datediff(max('Date Table'[Date]),today(),MONTH)=0,blank(),[Attrition % Dummy])
        ,CALCULATE([Attrition % Dummy],dateadd('Date Table'[Date],-1,year))
        )
    ))

[funderburkj91]

I'll keep that in mind - just add another calculate! haha. That fixed the overall annualized attrition calculation.

The last issue I am seeing is that this measure does not work when breaking it down by program. If you select the year 2019 on the slicer you created, the Attrition % Dummy does not equal the Annualized Attrit. Is there a way to use the current measure to accomplish that or do I need to build out a new measure that factors in the same logic and breaks it down by program?

[funderburkj91]

Update to my previous post:

I was incorrect in saying that the Attrition % should match the Annualized Attrition in the past. The subtotal for the Attrition % takes averages as you move up the date hierachy where Annualized Attrition sums  - which is what I want. Everything looks good in the dummy report.

This takes me to an issue in the actual report I am using this methodology in. I have added all of the appropriate measures and changes. When I drop in the ProgramName in that report, it stops the month value from extending in to Dec 2020 and caps it at April 2020. Any idea why that might be?

[DataZoe]

@funderburkj91 Glad that it is totaling correctly! Is there a bi-directional relationship between the tables?

[funderburkj91]

That was it - good call! Case closed.

Thanks for your help.