One of the most popular techniques to understand the direction of the metrics is to use a moving average (or rolling average). This technique will clearly show whether the metrics improve over time.

Although the rolling average is easy to use in Google Data Studio (GDS), the tool doesn’t allow to create the moving average as quickly as the rolling average, and users wonder how to use the correct moving average in GDS.

Therefore, I dedicate this article to showing how to use these two methods in Looker Studio, but first, let’s figure out the difference between these two highly comparative methods.

**Rolling Average and Moving Average Difference**

Even though the methods are so similar, there is a clear difference between them. Thus, let’s look at the example below.

We have a time-series dataset.

Date | Metric | Moving Average 5D | Rolling Average 5D |

1/1/23 | 12 | 12.0 | 12.0 |

2/1/23 | 21 | 16.5 | 16.5 |

3/1/23 | 12 | 15.0 | 15.0 |

4/1/23 | 31 | 19.0 | 19.0 |

5/1/23 | 41 | 23.4 | 23.4 |

6/1/23 | 21 | 25.2 | 23.0 |

7/1/23 | 12 | 23.4 | 21.4 |

8/1/23 | 21 | 25.2 | 21.4 |

9/1/23 | 12 | 21.4 | 20.3 |

10/1/23 | 31 | 19.4 | 21.4 |

11/1/23 | 41 | 23.4 | 23.2 |

12/1/23 | 21 | 25.2 | 23.0 |

13/1/23 | 12 | 23.4 | 22.2 |

14/1/23 | 21 | 25.2 | 22.1 |

15/1/23 | 12 | 21.4 | 21.4 |

The rolling average is the average of all values available when moving average is the average of the last 5 values. If there are no 5 values at that time, the moving average can be calculated just by taking the average of the available values – the same way as the rolling average.

However, in most analytics tools, it’s not calculated at all. For instance, if you calculate the 5-day moving average in Python Pandas, it will show it to you only from the 5th day.

This is the main difference between these two approaches. Now, after we have clarified it, let’s look at the ways of implementing them in Google Data Studio.

**How to create a rolling average in Google Data Studio**

As I mentioned earlier, creating a rolling average in Looker Studio is straightforward.

You need to create a line chart, add dimension and metric, and after that, metrics settings, select “Running calculation” and choose “Running average”.

**How to create a moving average in Google Data Studio**

However, it’s different for the moving average. To create a moving average, you should use the data blending method. Let’s look at this in detail.

- Add the two same data sources in Google Data Studio.
- Create a blend data and use the following configurations (see the screenshot above)
- Join operator – Cross join
- Name table #1 – main
- Name table #2 – spare

- Create a line chart and select the blended data source created early.
- Select date as a dimension
- Create a custom metric ‘Moving average” and write the following code (this code creates the 10-day moving average, but you can make 5, 7, 30, etc by adjusting numbers).

```
SUM(
CASE WHEN
DATE_DIFF(my_date (spare),my_date (main)) < 10
AND
DATE_DIFF(my_date (spare),my_date (main)) >= 0
THEN
my_value
ELSE
0
END
) /
(SUM(
CASE WHEN
DATE_DIFF(my_date (spare),my_date (main)) < 10
AND
DATE_DIFF(my_date (spare),my_date (main)) >= 0
THEN
1
ELSE
0
END))
```

After that, you will have a chart with the moving average line.

If you also prefer to see the moving average and the base metric on the same chart, use two line charts and add the second to the first one. This way, you will get one line chart with two lines.

**Wrapping Up**

Two solutions are used to see the direction of the metric: the rolling average and the moving average. However, although they look similar, they are clearly different. You can use a rolling average in Google Data Studio with a few clicks. However, to use a moving average, you should blend the data and use a custom formula presented in this article.

If you found this article helpful, please don’t hesitate to share it via social media and subscribe to my blog newsletter.

**Does Google Data Studio have a moving average?**

Although you can use a rolling average in Google Data Studio with a few clicks, you should blend data and use a custom formula to create a moving average.

First of all – I love your article and also found it very helpful.

But i got problems wie my custon dimension.

I copied your formula and replaced the variables (for me: ‘date’ and ‘views’).

But Looker know just splits my values of each day (for example 28 – switches every time refreash) and doesnt calcs the averages. One Problem, i do have sampled data, but i still could take the averages?

Hi Johann,

Thank you very much for reaching out!

Have you blended data sources before using the formula? I appreciate it if you could attach the dataset or the screenshot to your comment.

Best regards,

Ihar

Is there any prerequisite for this. Since the output that i am getting as a running average is wrong. I followed the same approach you are following

‘SUM(

CASE WHEN

DATE_DIFF(DATE(start_date (Table 2)),DATE(start_date (Table 1))) = 0

THEN

interest_amount_planned (Table 1)

ELSE

0

END

) /

(SUM(

CASE WHEN

DATE_DIFF(DATE(start_date (Table 2)),DATE(start_date (Table 1))) = 0

THEN

1

ELSE

0

END))’

Hi Kartik,

Looking at the code you sent above, I can tell you that you are not correctly following my solution. Could you please review my code above? If it doesn’t help, please consider sharing the dataset.

Great article Ihar!

Is it possible to make this for months? to get moving 3 months average?

many thanks

Hi Mr. Vakulski

Thank you for the article. I have been searching for this solution for the past few months. Thank you for writing this article.

I just have a slight struggle with using data sources directly from Google ads instead of exporting it to Google Sheets and working through the process. Is there a way to do the moving average directly from the Google ads data source using a blend data technique?

Hi Subhat,

I didn’t try to do it, but you can try. 🙂

Hi, thanks a lot for this.

I’m not sure why, but it’s not working with me. I have even tried doing it with your dataset, but the moving average is the same number as the value/metric number…

SUM(

CASE WHEN

DATE_DIFF(my_date (Spare),my_date (Main)) = 0

THEN

Metric

ELSE

0

END

) /

(SUM(

CASE WHEN

DATE_DIFF(my_date (Spare),my_date (Main)) = 0

THEN

1

ELSE

0

END))

Hi Felix,

Your formula is wrong.

Please compare it with mine, and you will find why it’s not working for you.

Best regards,

Ihar

Thank you for you solution. This is going to be a ridiculously stupid question, but I’m going for it…

Context: I’m creating a weekly website traffic report for a running 53 week view.

– Dimension: ISO week of ISO year

– Metrics: New users (Series 1 Stacked Bar), Returning users (Calc. Field (TU – NU)) (Series 2 Stacked Bar), Total users (Line with no line to list total users for the week (data label))

I want to add a 4th series, as the 3-month moving average using your formula provided:

SUM(

CASE WHEN

DATE_DIFF(ISO week of ISO year (spare),ISO week of ISO year (main)) = 0

THEN

Total users (main)

ELSE

0

END

) /

(SUM(

CASE WHEN

DATE_DIFF(ISO week of ISO year (spare),ISO week of ISO year (main)) = 0

THEN

1

ELSE

0

END))

Two issues I face, are the New users, Returning users and Total user amount aggregate as SUM. If I switch the aggregation of these three metrics to AVERAGE it’s more representative but not exact. The formula value (per week) then matches exactly the total users (AVG aggregation) per week, indicating something is not working correctly.

Hi Nick,

Thanks for reaching out.

Your formula is wrong.

Please compare it with mine, and you will find out why it’s not working for you.

Best regards,

Ihar