Moving Average

[Rotergnom2]

Hey,

I tried to go for the Moving Average building with PowerBI. 

The problem is that the moving average is calculated for the future, which does not make any sense. 

I wanted to do a six-month-rolling average with the quick measure (problem of above appeared), then with the formula = calculate(average([Sales]), DatesInPeriod([date];Lastdate([date]), -6, MONTH). Same Problem, again. So I do not know what is the problem. Perhaps somebody could offer me a simple sample PBIX data with a 6 month moving average over 2 years data..

[v-jiascu-msft]

Hi @Rotergnom2,

Could you please mark the proper answer as a solution?

Best Regards,

Dale

[v-jiascu-msft · Accepted solution]

Hi @Rotergnom2,

I would suggest you create a date table in your scenario. Please check out the attached demo and the measure below.

Measure =
VAR maxFactDate =
    CALCULATE ( MAX ( FactTable[Date] ), ALL ( 'Calendar' ) )
RETURN
    IF (
        MAX ( 'Calendar'[Date] ) > maxFactDate,
        BLANK (),
        CALCULATE (
            AVERAGEX (
                SUMMARIZE (
                    'FactTable',
                    'Calendar'[Date].[Year],
                    'Calendar'[Date].[Month],
                    "MonthTotal", SUM ( FactTable[Sales] )
                ),
                [MonthTotal]
            ),
            DATESINPERIOD ( 'Calendar'[date], LASTDATE ( 'Calendar'[date] ), -6, MONTH )
        )
    )

Best Regards,

Dale

[Greg_Deckler]

So, is your calculation correct other than the fact that it includes future estimations?