Forum Discussion

Rotergnom2's avatar
Rotergnom2
Regular Visitor
8 years ago
Solved

Moving Average

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's avatar
    v-jiascu-msft
    8 years ago

    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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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