Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Rolling average

Hi 
 
Can anyone see whats wrong with this formula? id like to get a 30 day rolling average, but i saw this online and tried to use it, just to get any kind of rolling average for now. but the result i get from this is the same number as teh Turnoer number, not an average at all.
 
Any ideas?
 
Turnover 30D Avg. = CALCULATE(
AVERAGEX(
FILTER(ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])),
onetouch_daily_account_summary_last_2_months[Turnover (€)])

)
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    If Turnover is a column rather than a measure, I think you should put the CALCULATE inside the AVERAGEX iterator in order to do the context transition.

     

    Turnover 30D Avg. =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
)

     

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    If Turnover is a column rather than a measure, I think you should put the CALCULATE inside the AVERAGEX iterator in order to do the context transition.

     

    Turnover 30D Avg. =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    ),
    CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    AlexisOlson  thank you for the reply, it works great now. much appreciated!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    AlexisOlson - just a follow up question if i may, how can i make this formula (which works fine) to be a 30 day average. at the moment its pulling the default total from the data set so id like to restrict this to Last 30 days only please.