A rolling 12-month total, also called trailing twelve months or moving annual total, adds up the current month and the eleven before it. In SQL and in most BI tools it needs care: a window of twelve rows is only twelve months if every month has a row. In the Semantic Layer it is a cumulative metric with a window, computed on the time spine, so the window is always twelve calendar months.

Copy and paste

metrics:
  - name: revenue_rolling_12m
    label: Revenue, last 12 months
    type: cumulative
    input_metric: revenue
    window: 12 months
    period_agg: last            # the 12 months ending on the last day of each month
mf query --metrics revenue,revenue_rolling_12m --group-by metric_time__month

Example

The same monthly sales as in the Snowflake and QuickSight versions of this guide, with no sales in August 2025:

Month revenue revenue_rolling_12m without period_agg
2025-07 80 730 650
2025-08 730 730
2025-09 120 850 730
2025-12 200 1,340 1,140
2026-01 110 1,350 1,340
2026-02 130 1,360 1,350
2026-03 100 1,370 1,360
2026-04 80 1,340 1,370
2026-05 470 1,680 1,340

December 2025 holds the total of the calendar year. Each later month adds the new month and drops the same month of the year before: January 2026 adds 110 and drops 100.

Why August doesn’t break it

A row-based window, such as ROWS BETWEEN 11 PRECEDING in SQL or windowSum in QuickSight, counts rows. Without an August row, January 2026’s twelve rows reach back to January 2025, thirteen months, and the total is 1,450 instead of 1,350. The cumulative metric works on dates: it sums the days within twelve months of each day of the time spine, so a month without orders simply adds nothing. August even gets its own row, with the rolling total of the twelve months ending on 31 August.

period_agg

The metric has a value for every day. Grouped by month, the default period_agg: first takes the value on the first day of the month: the 12 months ending on 1 March, which is essentially the total up to February. That is the “without period_agg” column, one month behind. period_agg: last gives the 12 months ending at month end. For the current month, “month end” is the last day in the time spine; filter the days to stop at today, as described in how to define a YTD or MTD metric.

The first months

At the start of the data the window holds less than a year: March 2025 shows the three months available. The metric doesn’t flag it. Start reports twelve months after the first data, or compare with the first full-year value.

Other windows

window accepts any interval: 7 days for a weekly rolling total, 28 days, 3 months. With agg: count_distinct on the input metric, a 30-day window counts the distinct customers of the last 30 days, as the dbt documentation’s weekly-customers example does with 7 days. window and grain_to_date can’t be combined in one metric. The other cumulative settings are in how to define simple, ratio, cumulative and derived metrics.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.

References