Year Over Year Comparison From a Monthly Excel Sheet

How is this month compared with the same month a year ago? Managers ask it of sales, traffic, cost and headcount. Month-on-month changes mislead in seasonal businesses, so the year-earlier comparison is the standard one. In Excel it means a second table, formulas that point twelve rows up and a lot of faith that nothing is missing. In SQL it is one query, provided the data is complete. This post builds the comparison two ways, handles the cases that break it (gaps, partial years, zeros) and states the choices a reader should know about. It is a spoke of doing proper analysis on Excel data with SQL.

The data

A sheet Monthly with one row per month:

If the sheet is order lines, first group to one row per month (summarizing a sales workbook with GROUP BY in SQL queries). Store the month as the first day of the month, a date, so it sorts and compares correctly.

Method 1: LAG with an offset of twelve

LAG(value, n) returns the value from n rows earlier in the window's order. With one row per month and no gaps, twelve rows earlier is the same month a year ago:

SELECT month,
       revenue,
       LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
       revenue - LAG(revenue, 12) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
             / NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0), 1) AS pct_change
FROM monthly
ORDER BY month;

This is short and fast. It is also fragile: LAG counts rows. In the sample data, the months from April to December 2024 are missing, so twelve rows before January 2025 is not January 2024, it is nothing, or the wrong month. The query returns plausible numbers that are for a different month. Use LAG only when you have proven the sequence is complete, which you can test:

SELECT COUNT(*) AS months,
       DATE_DIFF('month', MIN(month), MAX(month)) + 1 AS expected
FROM monthly;

If months is less than expected, there are gaps. Fix them first.

Method 2: a self join on the shifted date

Join the table to itself with the date moved back one year. It matches on the date, not on position, so missing months produce a null instead of a wrong number:

SELECT cur.month,
       cur.revenue,
       prev.revenue AS revenue_last_year,
       cur.revenue - prev.revenue AS change,
       ROUND(100.0 * (cur.revenue - prev.revenue) / NULLIF(prev.revenue, 0), 1) AS pct_change
FROM monthly cur
LEFT JOIN monthly prev
       ON prev.month = cur.month - INTERVAL 1 YEAR
ORDER BY cur.month;

(In PostgreSQL write cur.month - INTERVAL '1 year'.) The result:

month      | revenue | revenue_last_year | change | pct_change
-----------+---------+-------------------+--------+-----------
2024-01-01 | 100     | NULL              | NULL   | NULL
2024-02-01 | 120     | NULL              | NULL   | NULL
2024-03-01 | 110     | NULL              | NULL   | NULL
2025-01-01 | 130     | 100               | 30     | 30.0
2025-02-01 | 114     | 120               | -6     | -5.0
2025-03-01 | 0       | 110               | -110   | -100.0

The first year has no prior and shows nulls, which is honest. January 2025 is 30 percent above January 2024 (30 / 100). February is down 6, or 5.0 percent of 120. March is zero, down 100 percent; ask whether the zero is a real zero or a month not yet loaded (see below). Always left join: an inner join would drop 2024 entirely. The self join is the form I would use for reports, because its failure mode is a visible null.

Fill the gaps first

When months are missing from the data because nothing happened, build the full calendar and left join to it, with a stated rule for the empty months:

WITH calendar AS (
    SELECT unnest(generate_series(DATE '2024-01-01', DATE '2025-03-01', INTERVAL 1 MONTH))::date AS month
)
SELECT c.month, COALESCE(m.revenue, 0) AS revenue
FROM calendar c
LEFT JOIN monthly m ON m.month = c.month
ORDER BY c.month;

COALESCE(..., 0) treats a missing month as zero revenue. That is right for sales that did not happen and wrong for a month that was not reported. Keep the distinction if it matters: leave the null and add a flag. With a complete calendar, LAG(…, 12) becomes safe, and the self join and LAG agree, which is a useful test.

Partial years

In the middle of a year, comparing "this year so far" with "last year" as a whole is a classic error. Compare like with like: the same months.

SELECT 
  SUM(revenue) FILTER (WHERE month >= DATE '2025-01-01') AS ytd_2025,
  SUM(revenue) FILTER (WHERE month >= DATE '2024-01-01' AND month < DATE '2024-04-01') AS same_months_2024
FROM monthly;

Here year-to-date 2025 through March (130 + 114 + 0 = 244) is compared with January to March 2024 (330), a fall of about 26 percent. The comparison depends on whether March 2025 is complete. If the last month is partial, exclude it from both years, or say so. A report line that states "Jan to Feb vs Jan to Feb" is clearer than one that silently includes a half month. Do not annualize a partial period unless the business agrees on the method.

Percent change: the traps

Percent change is (new - old) / old. Its traps are all in the denominator.

• Zero prior value. Division by zero is an error in many databases and meaningless in all. NULLIF(prev, 0) returns null. Show "n/a" or "new", not a huge percentage.

• Tiny prior values. A move from 1 to 5 is a 400 percent rise that means little. Show the absolute change beside the percentage.

• Negative values. A change from -10 to -5 is an improvement but a negative percentage under the formula. For profit and similar measures, use the absolute change or define the sign convention.

• Mixed up directions. Percentage change from 100 to 50 is -50 percent, and from 50 to 100 is +100 percent. They are not symmetrical. Be clear which is the base.

• Percentage points versus percent. A rate moving from 10 percent to 12 percent is 2 percentage points, or a 20 percent increase. Label which one.

Make the comparison fair

Even a clean calculation can compare unlike things.

Calendar effects. A month with five weekends differs from one with four. February has 28 or 29 days; 2024 was a leap year, so February 2025 has one day fewer than February 2024. For businesses that depend on trading days, compare per trading day or add a column for the days in each month.

Moving holidays. Easter, Lunar New Year and similar events move between months from year to year. Compare quarters or rolling twelve months when they matter.

Changes in scope. A new store, a price change or an acquisition changes the base. Compare like for like, with a column marking the scope.

Restated figures. If last year's numbers were revised, use the current version of both.

A rolling twelve-month sum reduces seasonality and noise:

SELECT month,
       SUM(revenue) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS rolling_12m
FROM calendar_filled;

It needs a complete calendar and at least twelve months of history; the first eleven rows are partial sums and should be hidden or flagged.

The grouping that feeds this comparison is described in summarizing a sales workbook with GROUP BY in SQL queries, and the pivot-table version of shares and changes is in calculating percent of total in a pivot and in SQL.

Return it to Excel

Write the result to a file for charting. A clustered column chart of this year against last year per month, or a line chart of the percent change, makes the story visible. Include the basis in the sheet: the date range, the definition of the month, whether the last month is complete, and the time the data was extracted. A comparison without its basis gets misread.

Check the result

Verify two rows by hand. Check that the number of rows equals the number of months. Check that the sum of the current column for a complete year equals the year's total from the source. Confirm that months at the start have nulls and not zeros. Compare the LAG and join versions on a complete calendar; they should match.

When year over year is the wrong comparison

The business is changing quickly. A startup's month compared with a year ago is not a useful base. Use month on month or growth since a milestone.

The pattern is not annual. Weekly or lunar cycles call for other periods.

The data is short. With fewer than twenty-four months there is no comparison to make.

One event distorts the base. A pandemic month or a system outage in last year's data makes the comparison mislead. Note it, or compare with a different baseline.

See PostgreSQL's page on window functions and DuckDB's on LAG and the other window functions. To convert a small result to JSON, use the CSV to JSON converter.