xmetricRecipes

Recipe Tableau and SQL

How do I compare this year to date with last year, fairly?

Anchor on the last date in the data with { FIXED : MAX([order_date]) }, sum this year up to that date, and sum last year up to the same day: DATEADD('year', -1, [Max Date]). Comparing whole months instead goes wrong mid-month: in this shop it reads −2.9%, while on matched days sales grew 3.7%.

Updated

The data

Fern & Fig plant shop, daily sales to 17 October 2025

Synthetic dataorders.csv

An online houseplant shop with a spring rush and a bigger October, when people bring plants indoors. The data was last refreshed on 17 October 2025.

order_dateordersrevenue
2023-01-01502002.44
2023-01-02411493.18
2023-01-03391488.62
2023-01-04421748.48

Step 1

Each year, month by month

2025 tracks a little above 2024 most of the year, then falls off a cliff in the last month. Nothing happened to the shop: that month simply isn't over.

Tableau

SUM([revenue])
// MONTH([order_date]) on Columns, YEAR([order_date]) on Color

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
)
SELECT
  CAST (year(order_date) AS VARCHAR) AS year,
  month(order_date) AS month_of_year,
  strftime(MIN(order_date), '%b') AS month_name,
  SUM(revenue) AS revenue
FROM d
GROUP BY 1, 2
ORDER BY 1, 2

Rows

YearMonth of yearMonth nameRevenue
20231Jan$52,766
20241Jan$58,336
20251Jan$60,671
20232Feb$56,267
20242Feb$60,613
20252Feb$59,437
20233Mar$88,814
20243Mar$90,901
20253Mar$92,491
20234Apr$98,273
20244Apr$102,244
20254Apr$112,592
20235May$98,582
20245May$104,074
20255May$110,958
20236Jun$72,705
20246Jun$76,382
20256Jun$79,769
20237Jul$66,609
20247Jul$70,366
20257Jul$69,378
20238Aug$62,598
20248Aug$67,006
20258Aug$68,746
20239Sep$84,058
20249Sep$87,184
20259Sep$91,432
202310Oct$115,661
202410Oct$119,410
202510Oct$67,008
202311Nov$84,600
202411Nov$90,915
202312Dec$78,385
202412Dec$81,674

Step 2

Compare calendar years

A part year against a whole one. Down 19.5% looks like a crisis, and it's only the calendar.

Tableau

SUM([revenue])
// YEAR([order_date]) on Columns

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
), y AS (
  SELECT
    CAST (year(order_date) AS VARCHAR) AS year,
    SUM(revenue) AS revenue
  FROM d
  GROUP BY 1
)
SELECT
  year,
  revenue,
  revenue / LAG(revenue) OVER (ORDER BY year) - 1 AS yoy
FROM y
ORDER BY year

Rows

YearRevenueYoY
2023$959,318–
2024$1,009,1040.05
2025$812,482−0.19

Step 3

Compare each month with the same month last year

Month against month is fair for every finished month. The last one compares 17 days with a whole month and shows −43.9%.

Tableau

SUM([revenue]) / LOOKUP(SUM([revenue]), -12) - 1
// continuous MONTH([order_date]) on Columns; Compute Using: order_date

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
), m AS (
  SELECT
    CAST (date_trunc('month', order_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM d
  GROUP BY 1
)
SELECT
  month,
  revenue,
  revenue / LAG(revenue, 12) OVER (ORDER BY month) - 1 AS yoy
FROM m
ORDER BY month

Rows

MonthRevenueYoY
2023-01-01$52,766–
2023-02-01$56,267–
2023-03-01$88,814–
2023-04-01$98,273–
2023-05-01$98,582–
2023-06-01$72,705–
2023-07-01$66,609–
2023-08-01$62,598–
2023-09-01$84,058–
2023-10-01$115,661–
2023-11-01$84,600–
2023-12-01$78,385–
2024-01-01$58,33610.6%
2024-02-01$60,6137.7%
2024-03-01$90,9012.3%
2024-04-01$102,2444%
2024-05-01$104,0745.6%
2024-06-01$76,3825.1%
2024-07-01$70,3665.6%
2024-08-01$67,0067%
2024-09-01$87,1843.7%
2024-10-01$119,4103.2%
2024-11-01$90,9157.5%
2024-12-01$81,6744.2%
2025-01-01$60,6714%
2025-02-01$59,437−1.9%
2025-03-01$92,4911.7%
2025-04-01$112,59210.1%
2025-05-01$110,9586.6%
2025-06-01$79,7694.4%
2025-07-01$69,378−1.4%
2025-08-01$68,7462.6%
2025-09-01$91,4324.9%
2025-10-01$67,008−43.9%

Is the shop up or down this year?

Up. With data through 17 October 2025, 2025 is 3.75% ahead of the same days in 2024. The usual shortcut, filtering both years to January through October, says sales fell 2.87%: it keeps all 31 days of last October and only 17 of this one. October is this shop's biggest month, so the missing days outweigh a year of growth. Set Data runs to to 30 June and the two methods agree: at a month end there is no partial month to distort.

Data runs to

Common mistake

Filtering to whole months

Filtering both years to January through the current month keeps all of last year's last month and only part of this year's. Mid-month, that alone can turn growth into decline. Cut both years at the same day, not the same month.

Wrong
Point at the line to read it.
$0$250K$500K$750KRows in this order →

Tableau

MONTH([order_date]) <= MONTH([Max Date])
// on Filters

SQL

WITH b AS (
  SELECT MAX(order_date) AS max_date
  FROM input
)
SELECT year(order_date) AS year, SUM(revenue) AS revenue
FROM input, b
WHERE month(order_date) <= month(max_date)
GROUP BY 1
ORDER BY 1
Right
Point at the line to read it.
$0$250K$500K$750KRows in this order →

Tableau

DATEADD('year', YEAR([Max Date]) - YEAR([order_date]), [order_date]) <= [Max Date]
// on Filters

SQL

WITH b AS (
  SELECT MAX(order_date) AS max_date
  FROM input
)
SELECT year(order_date) AS year, SUM(revenue) AS revenue
FROM input, b
WHERE order_date + to_years(CAST (year(max_date) - year(order_date) AS INTEGER)) <= max_date
GROUP BY 1
ORDER BY 1

Step 4

Anchor on the last date in the data

One date drives the whole comparison: this year runs to it, last year runs to the same day a year earlier. A FIXED expression with no dimension returns the same value on every row, so filters on the view don't move it. If you want a date filter to move the anchor, add it to context.

Tableau

{ FIXED : MAX([order_date]) }
// name it [Max Date]

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
)
SELECT
  MAX(order_date) AS max_date,
  CAST (MAX(order_date) - INTERVAL 1 YEAR AS DATE) AS prior_year_cutoff
FROM d

Rows

Order datePrior year cutoff
2025-10-172024-10-17

Step 5

Keep the same days in every year

Move every date into the latest year and keep it only if it lands on or before the anchor. Each year now covers the same stretch of the calendar, so the bars compare like with like, and it works for any number of years.

Tableau

IF DATEADD('year', YEAR([Max Date]) - YEAR([order_date]), [order_date]) <= [Max Date] THEN [revenue]
END
// YEAR([order_date]) on Columns

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
), b AS (
  SELECT MAX(order_date) AS max_date
  FROM d
), y AS (
  SELECT
    CAST (year(order_date) AS VARCHAR) AS year,
    SUM(revenue) AS ytd_revenue
  FROM d, b
  WHERE order_date + to_years(CAST (year(max_date) - year(order_date) AS INTEGER)) <= max_date
  GROUP BY 1
)
SELECT
  year,
  ytd_revenue,
  ytd_revenue / LAG(ytd_revenue) OVER (ORDER BY year) - 1 AS yoy
FROM y
ORDER BY year

Rows

YearRevenueYoY
2023$744,809–
2024$783,1350.05
2025$812,4820.04

Step 6

YTD, last year to date, and growth

The two KPI fields most dashboards need. Because each is a row-level IF, they add up correctly at any level: by product, by region, by month.

YoY

3.7%

Tableau

SUM([YTD]) / SUM([PYTD]) - 1
// YTD: IF YEAR([order_date]) = YEAR([Max Date]) AND [order_date] <= [Max Date] THEN [revenue] END; PYTD: IF YEAR([order_date]) = YEAR([Max Date]) - 1 AND [order_date] <= DATEADD('year', -1, [Max Date]) THEN [revenue] END

SQL

WITH d AS (
  SELECT *
  FROM input
  WHERE order_date <= DATE '{knob.asof}'
), b AS (
  SELECT MAX(order_date) AS max_date
  FROM d
), k AS (
  SELECT
    SUM(revenue) FILTER (WHERE year(order_date) = year(max_date)) AS ytd,
    SUM(revenue) FILTER (WHERE year(order_date) = year(max_date) - 1 AND order_date <= max_date - INTERVAL 1 YEAR) AS pytd
  FROM d, b
)
SELECT ytd, pytd, ytd / pytd - 1 AS yoy
FROM k

Rows

YTDPytdYoY
81248153%78313515%3.7%

Common mistake

Anchoring on TODAY()

Open this report on 20 October and TODAY() gives last year three extra days that this year's data doesn't have yet. Growth drops from 3.75% to 2.13% with no change in sales. Anchor on the data's own last date.

Wrong
Point at a bar to read it.
0.02YoY

Tableau

[order_date] <= DATEADD('year', -1, TODAY())

SQL

SELECT
  SUM(revenue) FILTER (WHERE year(order_date) = 2025)
    / SUM(revenue) FILTER (WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-10-20')
    - 1 AS yoy
FROM input -- run on 20 October 2025
Right
Point at a bar to read it.
0.04YoY

Tableau

[order_date] <= DATEADD('year', -1, [Max Date])

SQL

WITH b AS (
  SELECT MAX(order_date) AS max_date
  FROM input
)
SELECT
  SUM(revenue) FILTER (WHERE year(order_date) = year(max_date))
    / SUM(revenue) FILTER (WHERE year(order_date) = year(max_date) - 1 AND order_date <= max_date - INTERVAL 1 YEAR)
    - 1 AS yoy
FROM input, b

Run it on your own data

Any table with a date and a measure works: orders, sign-ups, tickets, donations. Drop your CSV and pick the two columns; the steps recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, orders.csv, shows the shape.

Questions

Why use the last date in the data instead of TODAY()?

Data arrives late. An extract refreshed on Friday and opened on Monday has no sales for the weekend, but TODAY() still counts those days for last year. The last date in the data always matches what is loaded.

What about 29 February?

DATEADD('year', -1, ...) maps 29 February 2024 to 28 February 2023, and a 2025 cutoff of 17 October reaches back to 17 October 2024, which includes 29 February. So this year to date has 290 days and last year's has 291. Most teams accept the one day; if it matters, compare average revenue per day.

How do I handle a fiscal year that starts in April?

Shift every date back by the offset before you take the year: YEAR(DATEADD('month', -3, [order_date])) for an April start. Apply the same shift to the max date, and the rest of the recipe works unchanged.

Why does LOOKUP(..., -12) sometimes compare the wrong months?

It moves back 12 marks, not 12 months. If a product had no sales in some month, that month has no mark and the comparison slips. Fill in missing months first, or join each month to the same month a year earlier by date.

Sources