A running total that resets every year shows, at each point, the total of the year so far: year to date as a line. QuickSight has a function made for it, and the general running total can do the same with a partition.
Copy and paste
Year-to-date line (Order Date by month in the visual):
periodToDateSumOverTime(sum({Sales}), {Order Date}, YEAR)
Quarter or month to date as a line:
periodToDateSumOverTime(sum({Sales}), {Order Date}, QUARTER)
The same with runningSum, with a field {Order Year} = extract('YYYY', {Order Date}) in the visual:
runningSum(sum({Sales}), [truncDate('MM', {Order Date}) ASC], [{Order Year}])
Per region and year:
runningSum(sum({Sales}), [truncDate('MM', {Order Date}) ASC], [{Region}, {Order Year}])
Example
Monthly sales from the rolling 12-month total guide, with no sales in August 2025:
| Month | Sales | Running total, resets every year | Running total, never resets |
|---|---|---|---|
| Oct 2025 | 140 | 990 | 990 |
| Nov 2025 | 150 | 1,140 | 1,140 |
| Dec 2025 | 200 | 1,340 | 1,340 |
| Jan 2026 | 110 | 110 | 1,450 |
| Feb 2026 | 130 | 240 | 1,580 |
| Mar 2026 | 100 | 340 | 1,680 |
December 2025 shows 1,340, the total of the year, and January starts again.
Which one
periodToDateSumOverTime needs nothing but the date. The AWS blog post that introduced it shows the YTD
value of each month restarting in January. The period argument is required and fixed, so the line is
always year to date, even if the reader changes the date to quarters.
runningSum with a partition is more flexible:
- Fiscal years: partition by a fiscal year field instead of the calendar year; see how to calculate fiscal year to date.
- Groups: add
{Region}or any other dimension to the partition for one running total per group.
Put the partition fields in the visual as well. A year field in the colour well of a line chart, with the month number on the X axis, draws one line per year, the classic “this year against last year” cumulative chart.
Filters and missing months
Both are table calculations, so they see only the rows the filters keep. A filter that starts the visual in March 2026 makes March the first row of 2026, and its value 100 instead of 340: the January and February sales are gone. Filter whole years, or hide the months you don’t want with a filter on a table calculation.
A month with no sales has no row. August 2025 is missing from the line; the cumulative value simply stays flat between July and September, which is correct.
Checked against the Amazon Quick Sight function reference and the AWS Big Data Blog (October 2026). The example results were recomputed in DuckDB.
