Forum Discussion

yj1111's avatar
yj1111
Helper I
2 years ago
Solved

Moving Average Calculation for Forecasting

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!

 

 

Moving Average Calculation
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.



1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.