Moving Average Calculation for Forecasting

yj1111 — Sat, 13 Jul 2024 07:05:21 GMT

Dear all,

I would like to use Moving Average of actuals in the same period last year to predict my actuals in the rest of the year.

I use line and stacked column chart to show the values.

X-axis: Month Nr of year 2024 (only 2024 is selected)

Y-axis: 1) Actual costs from month 1-6 (column), 2) Forecast costs from 7-12 (line)

The Dax function I use for moving average calculation:

Actuals MA = AVERAGEX(DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-30,DAY],[ActualCosts])

Actuals Forecast = IF(ISBLANK([ActualCosts], CALCULATE([Actuals MA], SAMEPERIODLASTYEAR(Dates[Date])))

If I use Dates[Date] as X-axis, everything will work, however, the Actuals Forecast does not sum up on month level. How can I adjust my dax functions? (MovingAverage Dax Function is not availble in my PBI version)

Thanks in advance!

Re: Moving Average Calculation for Forecasting

Anonymous — Mon, 15 Jul 2024 04:56:31 GMT

Hi,yj1111 .I am glad to help you.
According to your description, you have correctly calculated the correct result from the measure, but it is not displaying correctly on the line/bar chart in months.
If my understanding is correct, you can refer to my test below
Here is my test data:

Here are two of your original measures

Actuals Forecast = IF( ISBLANK([ActualCosts]), CALCULATE( [Actuals MA], SAMEPERIODLASTYEAR(Dates[Date]) ) )

Actuals MA = AVERAGEX ( DATESINPERIOD ( Dates[Date], LASTDATE ( Dates[Date] ), -30, DAY ), [ActualCosts] )

To accommodate the fact that the Actuals MA uses a measure [ActualCosts], you don't give the exact code in your question.
So I modeled a simple measure (the column values in the table is [ActualCost])

Here is my solution idea:
I recreated a new column YearMonth
and used that column to replace the Date column that was originally displayed in visual
Wrote a new measure.
Aggregate your original end result for each day into months by month.
like this:

M_finalresult = VAR _yearMonth=MAX('Dates'[YearMonth]) RETURN SUMX( FILTER( Dates, Dates[YearMonth] = _yearMonth ), [Actuals Forecast] )

The fields displayed on the x-axis and y-axis of the discounted graph are as follows.

Note that my test results may not be suitable for your real data, so you need to modify the code I provided or create a new code according to your actual situation, because due to the different computing environment will affect the final results of the measure.
I hope my test results can bring you good ideas.

If you can provide a pbix file that doesn't contain sensitive data (which you can share with everyone via OneDrive), that will help solve your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Moving Average Calculation for Forecasting in DAX Commands and Tips

Moving Average Calculation for Forecasting

Re: Moving Average Calculation for Forecasting