Rolling Averages and Running Totals in Data Studio

• Running totals: built in
• Rolling averages: three approaches
• Pitfalls I check for
• Which one I use when

Daily numbers are noisy. A Monday dip or a one-day spike can dominate a Data Studio (formerly Looker Studio) chart and send a meeting off in the wrong direction. Rolling averages and running totals are the two tools I use most to calm that noise down. I’m Harsh Khubchandani, an independent Data Studio consultant in New Delhi, and this is how I add them without breaking the rest of the report.

§Running totals: built in

For a cumulative view, such as month-to-date revenue or year-to-date leads, Data Studio has a built-in option.

1. Select the metric in a time series or table.
2. Click the pencil or the metric’s options.
3. Under Running calculation, choose Running sum.

The chart now shows a running total that resets at the start of the date range. If the date range is “This month”, it is month-to-date. Pair it with a target line from a parameter or a Google Sheet and readers can see whether they are on pace.

The same menu also offers running average, count, minimum and maximum. Running average is useful but often misunderstood: it averages everything from the start of the date range, so it gets smoother and slower to react as the month goes on. That is not the same as a 7-day rolling average.

§Rolling averages: three approaches

1. The trendline option. On a time series chart, the Style panel has a Trendline setting per series, which includes a moving average option with a chosen number of periods. It is the quickest way to draw a 7-day smoothed line next to daily values. The limitation is that it is visual only: you can’t show the value in a scorecard or a table.

2. Calculate it in the source. For any rolling metric that needs to appear as a number, I compute it upstream. In BigQuery that is a window function along these lines:

AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

In a Google Sheet, an AVERAGE over the previous seven rows in a helper column does the same. The result arrives in Data Studio as a normal field and works in any chart.

3. Weekly granularity instead. Sometimes the simplest fix is to change the date dimension to ISO Year Week. Weekly totals remove the day-of-week pattern without any calculation at all, and readers find them easier to discuss than a smoothed line.

§Pitfalls I check for

Rolling averages need complete days. If the date range starts on the 1st, the first six days of a 7-day average don’t have enough history. When calculating upstream, I extend the query window back by six days and filter the extra days out after the calculation.

Ratios shouldn’t be averaged. A rolling conversion rate should be rolling conversions divided by rolling sessions, not the average of seven daily rates. Averaging daily rates gives a quiet day the same weight as a busy one.

Today is usually incomplete. Including the current partial day drags rolling lines down at the right edge. I end most date ranges at “yesterday”.

Filters change running totals. A running sum is calculated after filters are applied. That’s normally what you want, but it means the same chart shows different totals for different filter selections. A one-line note under the chart avoids confusion.

Comparison periods. Running sums with a previous-period comparison work well for “this month vs last month to date”, but check that both periods have the same number of days in the view.

§Which one I use when

• Month-to-date or year-to-date progress: running sum.
• Daily chart that people overreact to: trendline moving average.
• Rolling figure shown as a number or used in alerts: calculated upstream.
• Day-of-week pattern drowning the trend: weekly granularity.

Smoothing should make the trend easier to read, not hide real changes. I always keep the daily values available somewhere, even if only as a faint line behind the average.

If your team keeps reacting to noise in a daily report, I offer a free 30-minute review and a fixed written price. Details at datastudioconsultant.com.