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 point then carries a full year, so the line is free of seasonality and a change in trend shows as soon as it starts. In QuickSight it is windowSum over a monthly visual.

Copy and paste

Rolling 12 months (Order Date by month in the visual):

windowSum(sum({Sales}), [truncDate('MM', {Order Date}) ASC], 11, 0)

Only when the window holds 12 months:

ifelse(
  windowCount(sum({Sales}), [truncDate('MM', {Order Date}) ASC], 11, 0) = 12,
  windowSum(sum({Sales}), [truncDate('MM', {Order Date}) ASC], 11, 0),
  NULL
)

A position from the latest month, to filter on (1 = latest):

rank([truncDate('MM', {Order Date}) DESC])

A single figure, the 12 months up to a reference date (Datetime parameter AsOfDate):

sumIf({Sales}, {Order Date} >= addDateTime(-11, 'MM', truncDate('MM', ${AsOfDate}))
              AND {Order Date} < addDateTime(1, 'DD', ${AsOfDate}))

11, not 12

windowSum(measure, [sort], start, end) adds the rows from start rows above the current one to end rows below it, and the current row is always included. Twelve months are the current one and eleven above: 11, 0. The AWS reference shows windowSum(sum(Revenue), [SaleDate ASC], 12, 0) as a “trailing 12-month sum”, which adds up 13 rows.

Every month needs a row

The indexes count rows, not months. A month without sales has no row, and the window silently reaches one month further back. The example has no sales in August 2025, the same data as the Snowflake version of this guide:

Month Sales 11, 0 without August 11, 0 with August at 0 12, 0 with August at 0
Dec 2025 200 1,340 1,340 1,340
Jan 2026 110 1,450 1,350 1,450
Feb 2026 130 1,480 1,360 1,480
Mar 2026 100 1,460 1,370 1,460

Only the middle column is correct. Without the August row, January 2026’s twelve rows run from January 2025, thirteen calendar months. The wrong numbers look plausible, which is what makes the error dangerous. Give the dataset a row for every month: join a calendar of months to the sales, or fill the gaps in the source query.

The first eleven months

At the start of the data the window holds fewer than twelve months, and windowSum adds up whatever is there: March 2025 shows three months. The second formula returns the rolling total only when windowCount finds twelve rows, which, with a row for every month, means twelve months.

Showing only the last 12 points

A date filter “last 12 months” removes the history the window needs: the first visible month would sum one month. Table calculations are computed before filters on table calculations, though, so:

  1. Leave {Order Date} unfiltered, or filter it to 24 months.
  2. Create the rank field above, add a filter on it and keep values from 1 to 12.

The visual shows twelve points, each computed over a full year of data.

Related guides

Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.

References