cancel
Showing results for
Did you mean:
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.

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
Community Support

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.

9 REPLIES 9
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

https://allure-analytics.com/

Proud to be a Super User!

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] )
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.

Frequent Visitor

Unfortunately, i get an error message with your formula.

Super User

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

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

Super User

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

Frequent Visitor

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

Community Support

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.

Announcements

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

#### 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