Forum Discussion

jayjay0306's avatar
jayjay0306
Icon for Helper III rankHelper III
6 years ago
Solved

Rolling average last 3 months

Hi, I hope you can help me with a shallange: I have a Power BI report, where I need to make a measure (I don't have access to the source table) which calculates the rolling average for the last 3 m...
Anonymous's avatar
Anonymous
Not applicable
6 years ago

How about this?

 

Rolling Average 3 months =
VAR LastDate_ =
    LASTDATE ( Table[Calendar Day] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Table'[Month] ); CALCULATE ( SUM ( 'Table'[Sales] ) ) );
        FILTER (
            ALL ( Table );
            [Calendar Day] <= LastDate_
                && [Calendar Day] > DATEADD ( LastDate_; -3; MONTH )
        )
    )

 

Similar to yours, but i changed the table for AVERAGEX to iterate over to the month values. Also changed the calcualte filter a little bit.

jytech's avatar
jytech
Icon for Helper I rankHelper I
5 years ago

Anonymous what if I have a separate Date Table separate from my Facts Table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    jytech, it should be the same as long as you have set up a relationship between the tables. This is the preferred way to set up the model. Then filter the date table instead of the fact table in the measure.