How to handle 29 February in a prior year comparison in QuickSight

29 February exists once every four years, so every comparison with “the same date last year” has to decide what to do with it. In QuickSight the question starts earlier: the AWS documentation does not say what addDateTime(-1, 'YYYY', ...) returns for 29 February. The formula below never asks it.

Copy and paste

The same date last year, with a month-end rule (save as {AsOf LY}):

ifelse(
  extract('MM', addDateTime(1, 'DD', ${AsOfDate})) <> extract('MM', ${AsOfDate}),
  addDateTime(-1, 'DD', addDateTime(-1, 'YYYY', addDateTime(1, 'DD', ${AsOfDate}))),
  addDateTime(-1, 'YYYY', ${AsOfDate})
)

Last year’s YTD up to that date:

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

Daily comparison with the same weekday last year (364 days earlier):

periodOverPeriodLastValue(sum({Sales}), {Order Date}, DAY, 364)

AsOfDate is a Datetime parameter; replace it with truncDate('DD', now()) for a live dashboard.

How the first formula works

The condition asks whether tomorrow is in another month, that is whether the reference date is the last day of its month.

• Month end: go to the next day, which is the first of a month, move back a year, which is always a valid date, and step back one day. The result is the last day of the same month last year.
• Any other day: move back a year directly. Only 29 February could be missing in the previous year, and 29 February is always a month end, so this branch never meets it.

The third row is the reason for the rule. On 28 February 2025 the month is complete, and so should last year’s February be: the comparison runs to 29 February 2024, not 28 February. A plain “minus one year” gives two periods of 59 days, which is like for like by days but compares a complete February with a February minus its last day.

Daily charts

periodOverPeriodLastValue with YEAR, 1 on a daily visual compares each day with the same calendar date last year, and 29 February has none in a non-leap year. How the visual shows that day depends on how QuickSight matches it; check it in a table before publishing.

Comparing with the date 364 days earlier sidesteps the question: 52 weeks back always lands on the same weekday and maps every day to exactly one day. Friday 15 May 2026 is compared with Friday 16 May 2025. It is the usual choice for retail and any business driven by the day of the week. For period totals such as YTD, keep the date ranges: every day of each period is then counted exactly once.

Say which rule you used

Whatever rule you choose, write it in the visual’s description or the dashboard notes. The difference is small, but readers who check the numbers will find it.

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