---
title: "Fill missing dates for a 7-day rolling average | Oplema"
description: "Learn to build a date spine with Sequence in Strata so closed days count as zero, then use Rolling Calc for a true 7-day average instead of a 7-row one."
url: "https://oplema.com/learn/table-to-dashboard/fill-missing-dates"
updated: "2026-11-04"
---

Lesson 4 of 8 · Lab

# Fill missing dates and calculate a 7-day average

Sequence creates rows that don't exist yet, such as one per day. Rolling Calc averages over rows, so the days must all be there before a 7-row window means 7 days.

Marit wants the trend per shop. Old Town closes on Mondays, so its Mondays have no row in the daily table, and a closed day is easy to miss.

This live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep, starts from two tables at [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow): the daily table, 86 rows in a **Manual Input**, a table typed into the flow, and Marit's fleet table. **Union** passes the days to **Formula**, which turns an empty `bikes_out` into 0, and **Sort** orders them by shop and day. The lower branch waits for a list of days, and the **Formula** at the bottom right rounds `avg_7` to two places.

A 7-row average on Old Town's September 30: which days does it cover?

September 23 to 30 without Monday, September 28, when it was closed. Seven rows, eight days.

## Average, then fill the gaps

Put **Rolling Calc** between **Sort** and the rounding **Formula**, and read Old Town's 7-row average on September 30: 13.57. Then give every shop a row for every September day, so the same window covers 7 days.

**Check:** **Union** has 90 rows and still 1,717 bikes out, and Old Town's September 30 reads 11.86. **Sequence** makes the days; the **Lookup** and **Join** already find the missing ones.

If you're stuck

1. **Sort** is selected. In the palette, select **Transform** and drag **Rolling Calc** into the gap below it. Drag from **Sort**'s right dot to its left dot, and from its right dot to the **Formula** below it. If the Formula turns red, its wire comes from Sort rather than Rolling Calc.
2. Select **Rolling Calc**. Set **Operation** to **Running avg**, **Column** to `bikes_out` and **Window / offset** to 7. In **Output**, select all (Ctrl+A or Cmd+A) and type `avg_7`. Under **Partition By**, add `shop`.
3. Select the **Formula** at the bottom right and scroll its preview to Old Town's last row, September 30: `avg_7` is 13.57.
4. From **IO**, drag **Sequence** to the left of **Lookup**. Set **Start** to `2026-09-01` and **Stop** to `2026-09-30`. In **Column Name**, select all (Ctrl+A or Cmd+A) and type `hire_on`, then drag from its right dot to **Lookup**'s upper left dot.
5. Select **Lookup**: 90 rows, every day for every shop. Select **Join** and open **OUTPUT LEFT**: the 4 Mondays Old Town was closed.
6. Drag from **Join**'s top right dot, its Left output, to **Union**'s left dot. Select the **Formula** at the bottom right: Old Town's September 30 now reads 11.86.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What you should see

The four new days add 0 bikes out, so the total holds. Harbour and Station Road don't change: they opened every day.

## Rows are not days

Rolling Calc counts rows, not days. 13.57 is seven open days spread over eight calendar days. 11.86 counts the closed Monday as 0 bikes out. Marit asks how busy each week was, so a closed day belongs in the average as 0.

Join's Left output doubles as a gap list: before you fill anything, it shows exactly which days were missing. Here they're all Old Town's Mondays, so nothing unexpected is hiding.

Your line for Marit: "Old Town's last seven days averaged 11.86 bikes out a day, counting the Monday it was closed."

See the [Sequence](https://oplema.com/docs/strata/io/sequence) and [Rolling Calc references](https://oplema.com/docs/strata/transform/rolling-calc). For later: [Running totals and rolling averages](https://oplema.com/learn/data-preparation/rolling-windows) covers choosing a window.

## Common rolling average mistakes

- **Leaving out Partition By.** Harbour's last days then leak into the next shop's first average.
- **Averaging unsorted rows.** Rolling Calc takes rows in the order they arrive. Sort by shop and day first.

## Date spines in SQL and Excel

```
SELECT  s . shop ,  d . day ,  coalesce ( b . bikes_out ,  0 )  AS  bikes_out,
  round ( avg ( coalesce ( b . bikes_out ,  0 ))  OVER  ( PARTITION  BY  s . shop  ORDER BY  d . day
    ROWS  BETWEEN  6  PRECEDING  AND  CURRENT  ROW ),  2 )  AS  avg_7
FROM  generate_series ( DATE  '2026-09-01' ,  DATE  '2026-09-30' , INTERVAL  1  DAY ) d( day )
CROSS JOIN  fleet s
LEFT JOIN  daily b  ON  b . shop  =  s . shop  AND  b . hire_on  =  d . day ;
```

`generate_series` is SQL's Sequence and `CROSS JOIN` is Lookup's Every Row. In Excel, `=AVERAGEIFS` over a date range averages the days that exist; to count closed days as 0, list every date first.
