Running Average in Google Data Studio

Question

Monthly Running Average with Daily SUMS on Graph

I have data as Follows (SAMPLE):

• There would be multiple Orders Per Day.
• Need to Track Number of Orders per Day. In a Graph. (This is Straight for forward with Order Column Defaulting to 1 and then summing it.)

The thing that I would like to achieve is additional metric of 30-Day Moving average of along with the Daily Total of WorkOrders

Sample of what I have already achieved in Data Studio:

Along with it, I want Pseudocode

SUM ( Order 
WHEN 
DATE =( DATEBETWEEN ( CurrentRowDATE , CurrentRowDate - 30) ) 
) / 30

This would be the Average of Orders Per Day for the Past 30 Days.

Would Really Appreciate any Pointers. Appreciate your help in Advance.

Answer 1

You can do this by blending your data source with itself using a cross join. I used this sample data on Google Sheets:

Created the join:

Then created the table:

The formula for creating the running average:

SUM(
  IF(
    date (Table 2) BETWEEN DATETIME_SUB(date (Table 1), INTERVAL 2 DAY) AND date (Table 1),
    orders (Table 2),
    0
  )
) / 3