# Rolling and Moving Average in Google Data Studio (Looker Studio)

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.

Contents

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

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.

2. Create a blend data and use the following configurations (see the screenshot above)
1. Join operator – Cross join
2. Name table #1 – main
3. Name table #2 – spare
3. Create a line chart and select the blended data source created early.
1. Select date as a dimension
2. 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.

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.

## 7 Replies to “Rolling and Moving Average in Google Data Studio (Looker Studio)”

1. Johann Meier says:

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?

1. Ihar Vakulski says:

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

2. Kartik Ostwal says:

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

1. Ihar Vakulski says:

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.

3. Valentina says:

Great article Ihar!
Is it possible to make this for months? to get moving 3 months average?
many thanks

4. Subhat says:

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?

1. Ihar Vakulski says:

Hi Subhat,

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