How to calculate YTD and MTD in QuickSight

Year to date (YTD) adds up everything from 1 January to today; month to date (MTD) from the first of the month. QuickSight has two families of functions for it. periodToDateSum returns one number, for KPIs. periodToDateSumOverTime returns a cumulative value for every point of a time series, for charts.

Copy and paste

YTD and MTD up to now, for KPIs:

periodToDateSum({Sales}, {Order Date}, YEAR)

periodToDateSum({Sales}, {Order Date}, MONTH)

Up to a fixed date, for testing or a frozen report:

periodToDateSum({Sales}, {Order Date}, YEAR, parseDate('2026-05-15'))

YTD trend, restarting every January (Order Date by month in the visual):

periodToDateSumOverTime(sum({Sales}), {Order Date}, YEAR)

YTD with explicit bounds (Datetime parameter AsOfDate):

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

Replace 'YYYY' with 'MM' for MTD and 'Q' for QTD.

Example

Seven orders, the same as in the Snowflake version of this guide:

Order Date	Sales
2025-12-30	200
2026-01-10	100
2026-03-05	150
2026-04-08	80
2026-05-02	120
2026-05-15	50
2026-05-20	300

As of 15 May 2026:

YTD	QTD	MTD
500	250	170

The order of 30 December belongs to the previous year and the one of 20 May comes after the end date, so neither is counted.

KPI or trend

periodToDateSum is an aggregation: it returns one value for the period up to the end date, which is what a KPI visual needs. The end date is optional and defaults to now(), the moment the dashboard loads.

periodToDateSumOverTime is a table calculation. With the date by month in the visual it returns:

Month	Sales	YTD over time
Dec 2025	200	200
Jan 2026	100	100
Mar 2026	150	250
Apr 2026	80	330
May 2026	470	800

The value restarts in January, as the AWS blog post describes. Each row covers its whole month, so May includes the order of 20 May: the trend shows complete months, the KPI stops at its end date. February has no sales and therefore no row.

Which end date

now() suits data that is updated during the day. For SPICE datasets it is UTC, unless the analysis has a time zone set.
Data loaded overnight has nothing for today yet. Use yesterday as the end, which is easiest with the sumIf version and an AsOfDate parameter whose default is the relative date Yesterday.
A date chosen by the reader: see how to let users pick the “as of” date for YTD.

The sumIf version ends the range before the following day, so it includes the whole end day on timestamp fields too, and its bounds are visible in the formula.

Date filters

A filter on {Order Date} applies before these functions. A sheet filtered to the last 30 days returns a “YTD” of at most 30 days. Leave the date out of the filters of YTD visuals, or filter to a range that contains the whole period.

Related guides

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