How to calculate YTD growth % vs prior year in dbt (derived metric)

Year-to-date growth is the fair way to judge the current year while it is still running: 1 January to 15 May 2026 against 1 January to 15 May 2025. In the Semantic Layer it takes two pieces you already have: a YTD metric and an offset_window of one year.

Copy and paste

metrics:
  - name: revenue_ytd
    type: cumulative
    input_metric: revenue
    grain_to_date: year
    period_agg: last

  - name: revenue_ytd_last_year
    label: Revenue YTD, last year
    type: derived
    expr: revenue_ytd_ly
    input_metrics:
      - name: revenue_ytd
        alias: revenue_ytd_ly
        offset_window: 1 year

  - name: revenue_ytd_growth
    label: YTD growth vs prior year
    type: derived
    expr: (revenue_ytd - revenue_ytd_ly) / nullif(revenue_ytd_ly, 0)
    input_metrics:
      - name: revenue_ytd
      - name: revenue_ytd
        alias: revenue_ytd_ly
        offset_window: 1 year

As of a date:

mf query --metrics revenue_ytd,revenue_ytd_last_year,revenue_ytd_growth --group-by metric_time__day \
  --where "{{ TimeDimension('metric_time', 'day') }} = '2026-05-15'"

metric_time__day      revenue_ytd    revenue_ytd_last_year    revenue_ytd_growth
-------------------  -------------  -----------------------  --------------------
2026-05-15T00:00:00            590                      480              0.229167

Up to 15 May, 2026 sold 590 against 480 in the same days of 2025: +22.9%. The order of 20 May 2025 is outside last year’s period, as it should be.

By month

mf query --metrics revenue_ytd,revenue_ytd_last_year,revenue_ytd_growth --group-by metric_time__month \
  --where "{{ TimeDimension('metric_time', 'day') }} between '2026-01-01' and '2026-05-15'"

The offset is applied day by day before period_agg: last picks each month’s value, so May compares 15 May with 15 May: like for like, including the current incomplete month.

Use –where, not –start-time

The same query with --start-time 2026-05-15 --end-time 2026-05-15 returned 590 for this year and nothing for last year. The time limits are pushed into the data the cumulative metric reads, so the 2025 rows the offset needs are gone. A --where filter on metric_time is applied after the shift and keeps them. In BI tools, prefer a filter on the date over a “start date” setting for these metrics, and check one known value.

Why not divide by last year’s total

Comparing this year to date with last year’s full total is the classic distortion: in May, 590 against the full 2025 total of 1,340 looks like a 56% drop. The offset YTD keeps both sides on the same calendar days.

Products, regions and new items

Group by any dimension as well, such as customer or customer__country. As of 31 March 2026, by customer:

  customer    revenue_ytd    revenue_ytd_last_year    revenue_ytd_growth
----------  -------------  -----------------------  --------------------
        10                                     100
        11            110                      120            -0.0833333
        12            100                       90             0.111111
        14            130

• Customer 14 is new: no last-year value and no growth, which is right: growth from nothing is not a percentage.
• Customer 10 has bought nothing yet this year, and the growth is empty too, because her YTD is NULL, not 0. If such rows should show −100%, coalesce the current side in expr: (coalesce(revenue_ytd, 0) - revenue_ytd_ly) / nullif(revenue_ytd_ly, 0). With it, customer 10 returns −1.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.