Moving Average Calculation for Forecasting

[yj1111]

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!

[v-jtian-msft · Accepted solution]

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.