DAX implementation of Holt-Winters Additive - Help working around Recursion

[Sohrab]

Our users love the forecasts that the Power BI visuals provide.  However, we need to be able to see the values of the forecasted values in a Power BI visual, i.e. a table, as well as to be able to do more with those values.

Sandeep Pawar has a great post about the underlying methodology used by Power BI: ETS(AAA) 

https://pawarbi.github.io/blog/forecasting/python/powerbi/forecasting_in_powerbi/2020/04/24/timeseri...

Charles Zaiontz from Real-Statistics.com has excellent working models in Excel of Holt-Winters Additive <-> ETS(AAA)

Formulas:

https://www.real-statistics.com/time-series-analysis/basic-time-series-forecasting/holt-winters-addi...

Excel file with example (see sheet HoltWinters6):

https://www.real-statistics.com/wp-content/uploads/2022/03/Real-Statistics-Time-Series-Examples.xlsx

The method is, unfortunately for enthusiastic but (so far) unsuccessful DAX practitioners, *recursive*

Having read through all of @Greg_Deckler 's writing on working around implementing recursion in DAX, as well as @AlexisOlson 's great StackOverflow posts https://stackoverflow.com/questions/61257536/how-to-perform-sum-of-previous-cells-of-same-column-in-... and https://stackoverflow.com/questions/60641059/dax-formula-referencing-itself/60656874#60656874 on closed-form implementations, we're still stuck, with all roads leading to the dreaded circular dependecy error.  I was momentarily excited thinking that the new OFFSET() function would help, but the DAX engine wasn't fooled.

Help me @Greg_Deckler , you're my only hope!

[ttrebilcock]

power bi cannot do a real holt winters.

it is easy to do in excel.

in a worksheet set 3 cells to be labels Level, Trend, Season. put 0.5's as values next to them. these value cells will be used by Solver with a min of 0 and a max of 1.

colmn a can be yoir dates and column b the values. its best to have 7-8 years of data. 2 years is too little to use time series.

column c make a 12 mo rolling average. this eould start with ypur 13th data point of course. each of these is your level. fill this diwn to the bottom of your data.

in the 25th data point, it is the cuurent level less the prior one 12 mo back. this is your trend. fill this down to the bottom.

next create a season column. i do this by making a column that always shows a 1, 2, 3 etc thru 12, so each row shows the period number. then in a columnmext to this, use sumif to sum the total of that period / countif of the count of them. example so at row 50 or so, it might be summing 3 period 10's devided by 3. this returns an average for that season point. subtract from this the level. theiretically ypur season, or gamma, should net to zero w sime a megative and some a positive. the holt winters uses level w subtracted or added value to calculate the season amount in the forecast.

next create a backtrst column.

start w data point 25.

=level times yoir Alpha or level control cell up top that says 0.5,     plus trend times Beta or the trend control cell up top, plus season value times Gamma pr yoir season control cell.

fill this diwn, its ok it will look weird at furst.

column next to this,

subtract the actuak data point from the forecast data point, putvthis in an ABS to maje it akways posituve. fill down.

so you are baxktesting all actuaks.

make one cell up near yoir 3 control cells that totaks your ABS values only diwn to the bottom on yoir actuaks.

now insolver

tell it to change three cpntrol cells at yhe top, alpha beta and gamma, (level, trebd, season) and minimize the total of your variances cell.

now just fill dien yoir forecast column into the future.

its not perfect because true holt winters does what is called decomposition to first remove trend BEFORE creating season.

the excel approach, i used it with the State of Maine to forecast the Medicaid ask for the legislatire biennium ask and it was tested by a slew of people, and it ended up backtesting afyer i created a 2013 forecast for 2013 and 2014 usibg actuaks from 2003-2012. it backtested after a year went by in 2013 very well, it was off only 0.5%. in that model i had invented a complocated way to decompose the level first to get the real seasob. 

good luck!

ild tecommend first learnibv how holt winters works, the equation.

[AlexisOlson]

This looks at least as messy as another multi-column recursive process I found a closed form of:
https://www.linkedin.com/pulse/hammering-recursive-screw-power-math-dax-alexis-olson/

Without a significant amount of effort I can't even tell if it's possible, so I really don't recommend attempting this in DAX without exhausting other approaches first.

[Sohrab]

@tamerj1 - for the purpose of getting things done, you can assume that the initial values of cells u (Excel cell G9), v (Excel cell H9) and s (Excel cell I9) in the Excel are constants that you can "hardcode", ex. 14.25 and 1.875 respectively can be set as the "base" values (in reality, ther is a way to derive them, but that's not the part where I'm stuck).

G10 however takes G9, H9 and "an older I".  Similarly, H10 takes G9 and H9 as inputs.

In general, both of u_i and v_i take u_i-1 and v_i-1 as inputs.

[tamerj1]

Hi @Sohrab 

How is G9 calculated?