“Same month last year” is a derived metric that reads another metric shifted in time. offset_window: 1 year on an input metric moves its values forward by a year, so each period shows the value of the same period one year earlier, in the same row.

Copy and paste

metrics:
  - name: revenue_last_year
    label: Revenue, same period last year
    type: derived
    expr: revenue_ly
    input_metrics:
      - name: revenue
        alias: revenue_ly          # needed as soon as revenue is also used unshifted
        offset_window: 1 year
mf query --metrics revenue,revenue_last_year --group-by metric_time__month --start-time 2026-01-01
metric_time__month      revenue    revenue_last_year
--------------------  ---------  -------------------
2026-01-01T00:00:00         110                  100
2026-02-01T00:00:00         130                  120
2026-03-01T00:00:00         100                   90
2026-04-01T00:00:00          80                  110
2026-05-01T00:00:00         470                  130

For the growth in percent, see how to calculate growth % vs prior period.

How the offset works

MetricFlow computes revenue for every day, shifts the dates by offset_window, and joins the shifted values to the current ones on metric_time. It then aggregates to the grain of the query, so the same metric works by day, week, month or quarter. Any interval works: 1 month, 7 days, 52 weeks for the same weekday last year.

The offset is applied to metric_time, the metric’s default time dimension, and the query must group by it. Grouping only by another date, such as order__shipped_date__month, fails:

The query includes a metric 'revenue_last_year' that specifies a time offset in input metrics: (...).
However, group-by-items do not include 'metric_time'.

A KPI tile with no date on it can’t show an offset metric either; give it metric_time at some grain and filter to the period you want.

Grouped by year

mf query --metrics revenue,revenue_last_year --group-by metric_time__year
metric_time__year      revenue    revenue_last_year
-------------------  ---------  -------------------
2025-01-01T00:00:00       1340
2026-01-01T00:00:00        890                 1340

2026 has data up to May, 2025 is complete, and the comparison suggests a 34% drop. Grouped by year or quarter, the current period is always partial. Compare year to date against the same dates last year instead; see how to calculate YTD growth % vs prior year.

The first year

2025 has no value for last year: the data starts in January 2025. A shifted metric also produces rows beyond the last data. With the time spine extended to December 2026, the query returned June to December 2026 with no revenue and last year’s values (100, 80, 120, 140, 150, 200). End the spine near today, or filter metric_time to the dates that have happened.

Leap days

A one-year offset maps 29 February to a date in the year before or after that has no 29 February. Totals by month and year are not affected; daily comparisons around the end of February are. The question is the same as in SQL; see how to handle 29 February year over year.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.

References