Recipe Tableau and SQL

Are my stores really growing, or did I just open more of them?

Compare this year with last year using only store-months that traded fully in both years. In Tableau, flag them with { FIXED [store], MONTH([sale_month]) : COUNTD(IF [trading_days] >= 20 THEN YEAR([sale_month]) END) } = 2; in SQL, GROUP BY store, month and keep groups with two qualifying years. At this bakery chain total sales grew 14.4% while same-store sales changed −2.8%: the new shops hid a shrinking core.

Updated October 30, 2026

The data

Crumb & Co. bakeries, monthly sales 2024 and 2025

Synthetic datastore-sales.csv

A ten-shop neighbourhood bakery chain. North Market opened in May 2024, Canal Wharf and Hilltop in 2025, and Quayside closed in July 2025.

`store`	`neighbourhood`	`sale_month`	`trading_days`	`revenue`
Harbour St	Old Harbour	2024-01-01	27	45049.77
Harbour St	Old Harbour	2024-02-01	25	44124.66
Harbour St	Old Harbour	2024-03-01	27	49846.99
Harbour St	Old Harbour	2024-04-01	26	51646.29

Step 1

Total sales, year over year

Sales are up and the chain has more shops. This is the number that goes in the press release, and on its own it can't tell you whether customers are buying more bread.

Tableau

SUM([revenue])
// Columns: YEAR([sale_month])

SQL

SELECT
  CAST (year(sale_month) AS VARCHAR) AS year,
  SUM(revenue) AS revenue,
  COUNT(DISTINCT store) : : INTEGER AS stores
FROM input
GROUP BY 1
ORDER BY 1

Rows

Year	Revenue	Stores
2024	$4,029,475	8
2025	$4,609,552	10

Step 2

Split it by store

Two bakeries have no 2024 bar at all, North Market's 2024 is only eight months, and Quayside's 2025 stops in July. Five of the six shops open all of both years sold less in 2025.

Tableau

SUM([revenue])
// Columns: [store]; Color: YEAR([sale_month])

SQL

SELECT
  store,
  CAST (year(sale_month) AS VARCHAR) AS year,
  SUM(revenue) AS revenue
FROM input
GROUP BY ALL
ORDER BY store, year

Rows

Store	Year	Revenue
Canal Wharf	2025	$482,182
Elm Park	2024	$462,133
Elm Park	2025	$454,848
Harbour St	2024	$615,550
Harbour St	2025	$576,999
Hilltop	2025	$159,427
Kings Row	2024	$541,214
Kings Row	2025	$554,389
Mill Lane	2024	$543,828
Mill Lane	2025	$519,883
North Market	2024	$364,080
North Market	2025	$608,356
Quayside	2024	$353,146
Quayside	2025	$159,871
Riverside	2024	$440,383
Riverside	2025	$425,719
Station Rd	2024	$709,141
Station Rd	2025	$667,880

Step 3

Keep only months that match

A store-month counts when the store traded at least {knob.min_days} days in that month in both years. The six shops open throughout count all 12 months. North Market counts from June, Quayside until June, and the two 2025 openings not at all.

Tableau

{ FIXED [store], MONTH([sale_month]) : COUNTD(IF [trading_days] >= 20 THEN YEAR([sale_month]) END) } = 2

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    trading_days,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) FILTER (WHERE trading_days >= 20) = 2
)
SELECT
  s.store,
  COUNT(DISTINCT c.month) : : INTEGER AS comparable_months
FROM s LEFT JOIN c ON c.store = s.store AND c.month = s.month
GROUP BY s.store
ORDER BY comparable_months DESC, s.store

Rows

Store	Comparable months
Elm Park	12
Harbour St	12
Kings Row	12
Mill Lane	12
Riverside	12
Station Rd	12
North Market	7
Quayside	6
Canal Wharf	0
Hilltop	0

Minimum trading days in a month20025

Step 4

Total growth against same-store growth, by month

Total growth stays above zero every month, lifted by the shops that weren't there a year earlier. Same-store growth sits below zero every month. Two lines from the same file, telling opposite stories.

Tableau

SUM(IF [Comparable] AND YEAR([sale_month]) = 2025 THEN [revenue] END) / SUM(IF [Comparable] AND YEAR([sale_month]) = 2024 THEN [revenue] END) - 1
// Columns: MONTH([sale_month])

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    trading_days,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) FILTER (WHERE trading_days >= 20) = 2
), g AS (
  SELECT
    s.month,
    SUM(revenue) FILTER (WHERE year = 2025)
      / SUM(revenue) FILTER (WHERE year = 2024)
      - 1 AS total_growth,
    SUM(revenue) FILTER (WHERE c.store IS NOT NULL AND year = 2025)
      / SUM(revenue) FILTER (WHERE c.store IS NOT NULL AND year = 2024)
      - 1 AS same_store_growth
  FROM s LEFT JOIN c ON c.store = s.store AND c.month = s.month
  GROUP BY s.month
)
SELECT month : : INTEGER AS month, measure, growth
FROM g UNPIVOT (growth FOR measure IN (total_growth AS 'Total', same_store_growth AS 'Same-store'))
ORDER BY month, measure

Rows

Month	Measure	Growth
1	Same-store	−4.3%
1	Total	12.1%
2	Same-store	−3.7%
2	Total	12.3%
3	Same-store	−3.2%
3	Total	25.6%
4	Same-store	−2.8%
4	Total	27%
5	Same-store	−4.7%
5	Total	19.3%
6	Same-store	−0.8%
6	Total	12.4%
7	Same-store	−1.3%
7	Total	6.8%
8	Same-store	−1.9%
8	Total	4.5%
9	Same-store	−2.2%
9	Total	12.3%
10	Same-store	−3.5%
10	Total	13.2%
11	Same-store	−4.1%
11	Total	13.6%
12	Same-store	−1.9%
12	Total	15.9%

Step 5

The two numbers for the year

Report both. Total growth says how big the business got; same-store growth says whether the shops you already had got better at selling. Here they point in opposite directions.

Tableau

Total: SUM(IF YEAR([sale_month]) = 2025 THEN [revenue] END) / SUM(IF YEAR([sale_month]) = 2024 THEN [revenue] END) - 1; Same-store: the same with AND [Comparable] inside each IF

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    trading_days,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) FILTER (WHERE trading_days >= 20) = 2
)
SELECT
  'Total' AS measure,
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1 AS growth
FROM s
UNION ALL 
SELECT
  'Same-store',
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1
FROM s JOIN c USING (store, month)

Rows

Measure	Growth
Total	14.4%
Same-store	−2.8%

Can total sales grow while every store shrinks?

Yes, and here they nearly do. Crumb & Co. grew 14.4% in 2025 by opening two bakeries and running a third, North Market, for its first full year. The shops it already had changed −2.8% on matching months. Five of the six bakeries open all of both years sold less. Total growth measures expansion; same-store growth measures demand. A chain can buy the first and still be losing the second.

Why does the opening month matter?

A store that opens mid-month trades only part of it, so a year later the same month looks like a boom. North Market traded 12 days in May 2024. Slide the minimum trading days to 0 and May jumps from a decline to growth; at 20 days, half months drop out.

Common mistake

Counting the opening month as comparable

North Market opened on 18 May 2024 and traded 12 days that month. Compare that half month with a full May 2025 and the chain's comp jumps from a decline to growth in May. Set the minimum trading days to 0 to see it, and Quayside's half-month closure drags July the other way.

Wrong

Point at a bar to read it.

Tableau

{ FIXED [store], MONTH([sale_month]) : COUNTD(YEAR([sale_month])) } = 2

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) = 2
)
SELECT
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1 AS may_same_store_growth
FROM s JOIN c USING (store, month)
WHERE month = 5

Right

Point at a bar to read it.

Tableau

{ FIXED [store], MONTH([sale_month]) : COUNTD(IF [trading_days] >= 20 THEN YEAR([sale_month]) END) } = 2

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    trading_days,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) FILTER (WHERE trading_days >= 20) = 2
)
SELECT
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1 AS may_same_store_growth
FROM s JOIN c USING (store, month)
WHERE month = 5

Common mistake

Matching stores by year instead of by month

Keeping every store that had any sales in both years lets North Market's eight 2024 months be compared with twelve 2025 months, and Quayside's twelve with seven. The flag has to match the store and the month, not just the store.

Wrong

Point at a bar to read it.

Tableau

{ FIXED [store] : COUNTD(YEAR([sale_month])) } = 2

SQL

WITH s AS (
  SELECT store, year(sale_month) AS year, revenue
  FROM input
), c AS (
  SELECT store
  FROM s
  GROUP BY store
  HAVING COUNT(DISTINCT year) = 2
)
SELECT
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1 AS same_store_growth
FROM s JOIN c USING (store)

Right

Point at a bar to read it.

Tableau

{ FIXED [store], MONTH([sale_month]) : COUNTD(IF [trading_days] >= 20 THEN YEAR([sale_month]) END) } = 2

SQL

WITH s AS (
  SELECT
    store,
    month(sale_month) AS month,
    year(sale_month) AS year,
    trading_days,
    revenue
  FROM input
), c AS (
  SELECT store, month
  FROM s
  GROUP BY store, month
  HAVING COUNT(DISTINCT year) FILTER (WHERE trading_days >= 20) = 2
)
SELECT
  SUM(revenue) FILTER (WHERE year = 2025)
    / SUM(revenue) FILTER (WHERE year = 2024)
    - 1 AS same_store_growth
FROM s JOIN c USING (store, month)

Run it on your own data

You need a store (or branch, franchise, SKU), a date and a measure. Drop your CSV and pick the columns; the steps recompute in your browser, and nothing is uploaded. Without a trading-days column, set the minimum to 0 and check your opening and closing months by hand.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, store-sales.csv, shows the shape.

Questions

What is the difference between same-store sales and like-for-like sales?

Nothing but the name. Same-store sales is the common US term, like-for-like the UK one, and comp sales or comparable sales the analyst shorthand. All three compare the same set of stores across two periods.

How long must a store be open before it counts?

Matching months, as on this page, needs only the sales file. Many retailers are stricter and wait until a store has been open 12 or 13 full months, because a new store keeps ramping up after its first month. With an opening date per store, add DATEDIFF('month', [open_date], [sale_month]) >= 12 to the flag.

Does this work for products or customers?

Yes. Swap stores for SKUs to separate catalogue growth from demand for existing products, or for customers to get revenue retention on the same set of accounts.

What about calendar shifts?

A month with one more Saturday than last year's moves sales on its own. Retailers that care compare by week number or a 4-5-4 retail calendar so weekdays line up.

Sources

Tableau: FIXED level of detail expressions checked October 30, 2026