How to calculate YTD growth % vs prior year in QuickSight

Growth compares this year’s year-to-date total with last year’s over the same dates: (this year − last year) / last year. The formula fits on one line. The work is in the rows where it does not apply, such as a product that did not exist last year.

Copy and paste

Start from the two measures of how to compare YTD with prior year YTD, {YTD} and {YTD LY}. Then:

Growth, empty when last year is zero (format as a percentage):

ifelse(
  coalesce({YTD LY}, 0) = 0, NULL,
  (coalesce({YTD}, 0) - {YTD LY}) / {YTD LY}
)

A label for the rows without a growth figure:

ifelse(
  coalesce({YTD LY}, 0) = 0 AND coalesce({YTD}, 0) > 0, 'New',
  coalesce({YTD}, 0) = 0 AND coalesce({YTD LY}, 0) > 0, 'Lost',
  'Comparable'
)

Growth that follows the direction of change when last year was negative:

ifelse(
  coalesce({YTD LY}, 0) = 0, NULL,
  (coalesce({YTD}, 0) - {YTD LY}) / abs({YTD LY})
)

Example

Three products, the same as in the Snowflake version of this guide. Apples sold in both years, Pears are new this year and Plums sold only last year. As of 15 May 2026:

• Pears get no growth figure, which is correct: growth from nothing is not a percentage. The status column says why the cell is empty.
• Plums show −100%: the product sold nothing this year.
• The total row is 320 against 260. An average of the product percentages would be a different and meaningless number, and Pears have none to average.

Why coalesce

For a product with no rows in one of the two periods, sumIf can return an empty value rather than 0. Then {YTD} - {YTD LY} is empty too, and Plums would lose their −100%. coalesce(..., 0) makes the missing side a zero. Testing coalesce({YTD LY}, 0) = 0 before dividing returns NULL for a zero base, instead of an error or an infinite value.

Don’t replace the NULL with 0: a growth of 0% means “no change”, the opposite of what happened to a new product.

Format

The formula returns a ratio, 0.25 for 25%. Apply the Percent format to the field, which multiplies by 100 for display. Don’t multiply by 100 in the formula as well.

The total row

These are aggregated calculated fields, so a table computes the total row from the totals of {YTD} and {YTD LY}, not by adding up the percentages. Check the total once in your table: it should read 23.1%, not the sum or the average of the rows above.

A negative base

Refunds can make last year’s net total negative. With −50 last year and 20 this year, the first formula gives −140%, a decline, although the result improved by 70. Dividing by abs({YTD LY}) gives +140%, so the sign follows the direction of the change. Many teams prefer to show no percentage at all for a negative base and to report the absolute difference instead.

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