Holt-Winters Forecasting in DAX

[Greg_Deckler]

No, you most certainly should not be doing this. But, I figured, what the heck. Some of the code is provided below. There is lots more code, well over 3,000+ lines. This solution is based on this article by Gregory Trubetskoy: Holt-Winters Forecasting for Dummies - Part III - Gregory Trubetskoy (grisha.org). I also included the 6 line R solution created by Brian Julius: Post | Feed | LinkedIn

Here is the video that explains the code.

Here is some of the code:

initial_trend = 
    VAR __slen = 12
    VAR __table = 
        ADDCOLUMNS(
            GENERATESERIES(0, __slen - 1, 1),
            "__sum", 
                DIVIDE(
                    MAXX(FILTER('Series', [Index] = [Value] + __slen), [Column]) - 
                        MAXX(FILTER('Series', [Index] = [Value]),[Column]),
                    __slen
                )
        )
    VAR __result = DIVIDE(SUMX(__table, [__sum]), __slen)
RETURN
    __result


Prediction = 
    VAR __slen = 12
    VAR __i = MAX('Indices'[Index])
    VAR __count = COUNTROWS(ALL('Series'))
    VAR __result = 
        IF(
            __i > __count - 1,
                VAR __m = __i - __count + 1
                VAR __smooth = MAX('Forecast71'[smooth])
                VAR __trend = MAX('Forecast71'[trend])
                VAR __mod = MOD(__i, __slen)
                VAR __seasonal = MAXX(FILTER('Seasonal71', [Value] = __mod), [__Value])
                VAR __result = (__smooth + __m * __trend) + __seasonal
            RETURN
                __result,
            BLANK()
        )
RETURN
    __result

[DataSmiths]

I don't think people truly appreciate how much skill this actually took to figure out. Having created this measure measures like this myself, I just have to say I respect the work and admire the skill! My video would have involved A LOT more cussing lol