cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
benparody
Frequent Visitor

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 (€)] )
)
1 ACCEPTED SOLUTION

Hi @benparody ,

 

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.

View solution in original post

9 REPLIES 9
ALLUREAN
Super User
Super User

Hi, @benparody 

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




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




benparody
Frequent Visitor

@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] )
Samarth_18
Super User
Super User

Hi @benparody ,

 

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


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


Thank you for your reply @Samarth_18 . 

 

Unfortunately, i get an error message with your formula. Capture.PNG

@benparody 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 )
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


@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 )
)
 
 

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

 


If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin


@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.

Hi @benparody ,

 

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.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors