How to calculate the 30-day rolling average?

Hi,

Could someone please help me calculate the 30-day rolling average of the "ratio" column by country for each day?

I have tried using the multi-row formula, but it's a bit confusing, and I'm not sure if I'm doing it correctly. Any guidance would be appreciated.

Thank you!

Attached sample of the data:

Que-Avg.xlsx

Datasets

Developer

Developer Tools

Macros

Dynamic Processing

Join

Optimization

Accepted answers

OllieClarke

Hey @AKPWZ

Here's how I'd do this:

The multi-row method can look back 30 rows, but that's not the same as 30 days in your data (it's also a massive pain to write such a long formula).
By scaffolding your dates to go back 29 days and then join your data onto itself with that, we can average that joined data to find an actual 30 day moving average.

If you need to find this average within countries (i.e. the 30 day moving average in DM) , then include country as a joining condition, but I've assumed that's not what you're after here...

Hope that helps,

Ollie

mvgavg.yxzp

OllieClarke

@AKPWZ

Yeah, so the generate rows makes a lookup of all the dates which would be in the 30 day window

We then use that lookup to join on the relevant ratio values

Then you can avg the Right_Ratio column to find your 30 day moving average.

Does that make sense?

All comments

OllieClarke

Ollie

mvgavg.yxzp

AKPWZ

Hi @OllieClarke Thank you Thank you so much

I just want to confirm that I understood the workflow you shared correctly. This workflow will:

Create a Date Range: For each date, look back 29 days to include a total of 30 days, including the current day.
Join Data: Merge your data with itself based on this 30-day period.
Calculate the Average: For each date and for each country, compute the average of the "ratio" values over these 30 days.
As you asked (If you need to find this average within countries (i.e. the 30 day moving average in DM) , then include country as a joining condition) and If that’s what I need, what changes should I make to the existing workflow?

This will provide a 30-day moving average for the "ratio" column.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703