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. 

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. 

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. 

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.