Forum Discussion
Helper IIIRolling average last 3 months
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.
Excellent Ulf, that solved it 🙂
Thanks.
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.
jayjay0306Hi 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
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
Excellent Ulf, that solved it 🙂
Thanks.
jytechHelper I
Anonymous what if I have a separate Date Table separate from my Facts Table?
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.