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.

     

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

Hi Ulf,

Brilliant! it works. 🙂

Thanks a lot.

The only remark I have is, that the rolling average doesn't react to the filters I have in the PBI report.

The value "Table[Sales]" is part of you calculation, but if I fx.filter on "Sales area" the "Sales" per month respond accordingly, but the rolling average remains the same. I find this a bit strange.

Can you tell me why?any ideas?

Thanks anyway.

Br,

Jakob

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Yes, it's because of ALL(Table) in the filter. All filters are removed to be able to access previous months. It should be possible to modify the CALCULATE / FILTER expression to only remove the filters on date/year/month. To do that easier and better I suggest creating a connected date table instead of having the date columns in the "fact" table. Dimensional models are usually easier to work with in Power BI! Then you can do the same thing as now but on the date table  - ALL([NewDateTable]) instead of ALL(Table).

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

    Excellent Ulf, that solved it 🙂

    Thanks.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Good to hear the problem is solved! By the way, you should mark my answer as the answer, not your own post... 🙂