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. 

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. 

DateMetricMoving Average 5DRolling Average 5D
1/1/231212.012.0
2/1/232116.516.5
3/1/231215.015.0
4/1/233119.019.0
5/1/234123.423.4
6/1/232125.223.0
7/1/231223.421.4
8/1/232125.221.4
9/1/231221.420.3
10/1/233119.421.4
11/1/234123.423.2
12/1/232125.223.0
13/1/231223.422.2
14/1/232125.222.1
15/1/231221.421.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”. 

Data Studio Running Average
Data Studio Running Average

How to create a moving average in Google Data Studio

Data Studio Data Blending
Data Blending To Create Moving Average in 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. 

  1. Add the two same data sources in Google Data Studio.
  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. 

Data Studio Moving Average with Base Metric
Data Studio Moving Average with Base Metric

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. 

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

  1. 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. 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. 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. 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. Great article Ihar!
    Is it possible to make this for months? to get moving 3 months average?
    many thanks

  4. 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?

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

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

Leave a comment

Your email address will not be published. Required fields are marked *