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. (€) = AVE...
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.

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 Now i dont have an error message but i get a blank answer.
     
    I changed this "AVERAGE ( onetouch_daily_account_summary_last_2_months[Turnover (€)] )" from a measure to reference the column (or else it wouldnt accept the formula)
     
    I dont know if that makes a difference or not. 
     
    Turnover Rolling Avg. Calc. (€) 2 =
    CALCULATE (
    AVERAGE ( onetouch_daily_account_summary_last_2_months[bet_eur] ),
    ALL ( 'Calendar' ),
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY )
    )
     
     
    • Samarth_18's avatar
      Samarth_18
      Community Champion
      4 years ago

      Anonymous It should work. Is it possible for you to share PBIX after removing sensitive data?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Samarth_18 It will take me ages to clean this file to get it to a place can send 😞

         

        Is this my only formula option or perhaps is there an alternate way using a different function perhaps? Maybe thats asking too much, but i wont be able to send the file just yet. hmm.