A rolling 12-month total, also called moving annual total or trailing twelve months, adds up the current month and the eleven before it. Every month then carries a full year of history, so the line is free of seasonality and a change in trend shows up as soon as it starts.

A small example

One order per month from January 2025 to March 2026, with nothing sold in August 2025.

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2025-01-15', 100), ('2025-02-15', 120), ('2025-03-15', 90),
  ('2025-04-15', 110), ('2025-05-15', 130), ('2025-06-15', 100),
  ('2025-07-15', 80),  -- no sales in August
  ('2025-09-15', 120), ('2025-10-15', 140), ('2025-11-15', 150),
  ('2025-12-15', 200), ('2026-01-15', 110), ('2026-02-15', 130),
  ('2026-03-15', 100);

The query

WITH Bounds AS (
  SELECT DATE_TRUNC('month', MIN(OrderDate)) AS FirstMonth,
         DATE_TRUNC('month', MAX(OrderDate)) AS LastMonth
  FROM Sales
),
Months AS (   -- one row per calendar month, with or without sales
  SELECT DATEADD(month, f.value::INT, b.FirstMonth) AS MonthStart
  FROM Bounds b,
       LATERAL FLATTEN(input => ARRAY_GENERATE_RANGE(
         0, DATEDIFF(month, b.FirstMonth, b.LastMonth) + 1)) f
),
Monthly AS (
  SELECT DATE_TRUNC('month', OrderDate) AS MonthStart,
         SUM(Amount) AS Sales
  FROM Sales
  GROUP BY 1
),
Filled AS (
  SELECT m.MonthStart, COALESCE(s.Sales, 0) AS Sales
  FROM Months m
  LEFT JOIN Monthly s ON s.MonthStart = m.MonthStart
)
SELECT MonthStart, Sales,
       IFF(COUNT(*) OVER (ORDER BY MonthStart
                          ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) = 12,
           SUM(Sales) OVER (ORDER BY MonthStart
                            ROWS BETWEEN 11 PRECEDING AND CURRENT ROW),
           NULL) AS Rolling12m
FROM Filled
ORDER BY MonthStart;

The work happens in the last SELECT. ROWS BETWEEN 11 PRECEDING AND CURRENT ROW sums the current row and the eleven rows before it. The CTEs make sure that those twelve rows are twelve calendar months:

The query returns fifteen rows. January to November 2025 have NULL as their rolling total, for the reason explained below; the last five rows are:

MonthStart Sales Rolling12m
2025-11-01 150.00 NULL
2025-12-01 200.00 1340.00
2026-01-01 110.00 1350.00
2026-02-01 130.00 1360.00
2026-03-01 100.00 1370.00

December 2025 holds the total of the calendar year 2025. Each later month adds the new month and drops the same month of the year before: January 2026 adds 110 and drops 100.

Why every month needs a row

Without the Months CTE, the window runs over the rows of Monthly directly. August 2025 has no row there, so from January 2026 onwards the twelve rows in the window cover thirteen calendar months:

MonthStart Without the month list With the month list
2025-12-01 1340.00 1340.00
2026-01-01 1450.00 1350.00
2026-02-01 1480.00 1360.00
2026-03-01 1460.00 1370.00

The numbers look plausible, which makes the mistake hard to spot. A month with no rows is common for a small product, a new customer or a region that closes for the summer. The same technique works for days: see how to fill missing days before a running total.

The first eleven months

The first months of the data do not have eleven months before them, so their sum covers less than a year. COUNT(*) over the same window counts the months that are actually there, and IFF returns NULL until there are twelve. Without that check, a chart of the rolling total starts with a ramp that looks like growth.

Variations

References