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-mo...
  • 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

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