Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

30 day rolling average

Hi

 

Would anyone be able to help me convert this formula into a "last 30 day rolling average"? I seem to be going in circles.

 

Thank you in advance!

 

Turnover Rolling Avg. Calc. (€) =
AVERAGEX (
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
),
CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
)
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you can try this code.

    Turnover Rolling Avg. Calc. (€) 2 =
CALCULATE (
    AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Date]
                > MAX ( 'Calendar'[Date] ) - 30
    )
)

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

9 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    You can try below code:-

    Turnover Rolling Avg. Calc. (€) =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )
    ),
    CALCULATE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
)

     

    Thanks,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for your reply Samarth_18 . 

       

      Unfortunately, i get an error message with your formula. 

      • Samarth_18's avatar
        Samarth_18
        Community Champion
        4 years ago

        Anonymous Try this:-

        Turnover Rolling Avg. Calc. (€) =
CALCULATE (
    AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] ),
    ALL ( 'Calendar' ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Samarth_18  So now there is no error message, but the result is blank. hmm.

     

    i did have to change the reference for this part, to the column, and not the measure, for it to accept it.  

     

    AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )
     
    changed to 
     
    AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] )
  • ALLUREAN's avatar
    ALLUREAN
    Solution Sage
    4 years ago

    Hi, Anonymous 

    You can try this one:

    Turnover Rolling Avg. Calc. (€) =

    VAR NumOfDays = 30
    VAR LastCurrentDate = MAX ( 'Calendar'[Date] )
    VAR Period = DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfDays, DAY )
    VAR Result =
    CALCULATE (
    AVERAGEX (
    VALUES ( 'Calendar'[Date] ),
    onetouch_daily_account_summary_last_2_months[bet_eur]), Period)

    RETURN
    Result