How to compare YTD with prior year YTD in QuickSight

A year-to-date total needs a benchmark, and the fair one is last year up to the same date: 1 January to 15 May against 1 January to 15 May. In QuickSight that means two measures with different date ranges in the same visual, and no date filter that removes last year.

Copy and paste

KPIs up to now:

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

periodToDateSum({Sales}, {Order Date}, YEAR, addDateTime(-1, 'YYYY', now()))

KPIs up to a reference date, with explicit bounds (Datetime parameter AsOfDate):

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

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

Save them as {YTD} and {YTD LY}. Then:

Difference:

{YTD} - {YTD LY}

Monthly chart (Order Date by month in the visual), a YTD series saved as {YTD over time}:

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

and its value in the same month last year:

periodOverPeriodLastValue({YTD over time}, {Order Date}, YEAR, 1)

Example

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

As of 15 May 2026:

The order of 16 May 2025 is left out by one day, as it should be, and so is the one of 20 May 2026. The full year 2025 adds up to 670: comparing this year’s 500 with it would suggest a 25.4% fall, while like for like sales grew by two thirds.

Two ways to write it

periodToDateSum with addDateTime(-1, 'YYYY', now()) as its end date is the short version for a live dashboard. The sumIf version shows both ranges in the formula and works with a reference date chosen by the reader. A user on the QuickSight community forum reported that passing a date parameter straight into periodToDateSum as the end date did not give the expected result, so for a date chosen by the reader the sumIf form is the safer one; see how to let users pick the “as of” date for YTD.

No date filter on these visuals

Both measures need last year’s rows. A sheet filter “this year” removes them before the calculation, and {YTD LY} drops to empty. Leave {Order Date} out of the filters of the visuals that show the comparison; the formulas pick the dates themselves.

The monthly chart

The second field takes the YTD series and looks up its value in the same month of the previous year, the kind of nesting the AWS blog post demonstrates with periodOverPeriodDifference. The lookups are by date, which has two consequences in the example:

• April is empty because April 2025 had no sales and therefore no row to look up. A dataset with a row for every month, even at zero, avoids the gap.
• May compares complete months. Both values include the whole of May, including the orders of 16 May 2025 and 20 May 2026. For the current, incomplete month, use the KPIs, which stop at the reference date.

As with every table calculation, the visual needs the 2025 months in its data even if you only want to display 2026.

Related guides

• How to calculate YTD growth % vs prior year
• How to handle 29 February in a prior year comparison
• How to compare an incomplete month with the same days of last month

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