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

  • jayjay0306's avatar
    jayjay0306
    6 years ago

    Excellent Ulf, that solved it 🙂

    Thanks.

     

yash09's avatar
yash09
Frequent Visitor
2 years ago

i m using dax for rolling avg 3 month and that gets is result 

 

Total sales 3MA = AVERAGEX(
         WINDOW(
            -[NR MONTHS  Value],REL,0,REL,
            SUMMARIZE(
                ALLSELECTED(dimDate),
                dimDate[Year],
                dimDate[Month],
                dimDate[Month Number]
            ),
             ORDERBY(dimDate[Year],ASC,dimDate[Month],ASC)
         ),[TOTAL SALES]
)