04-18-2023 09:09 AM
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
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