How to define a semi-additive measure in dbt (account balance, non_additive_dimension)

A table of daily balances adds up across accounts (the bank’s total on a day) but not across days: the balance of January is not the sum of 31 daily balances. Metrics like this are semi-additive. Stock levels, open tickets, headcount and subscription counts work the same way. non_additive_dimension tells the Semantic Layer which date not to sum over.

Copy and paste

models:
  - name: balances                      # one row per account and day
    semantic_model:
      enabled: true
    agg_time_dimension: balance_date
    columns:
      - name: account_id
        entity: {type: foreign, name: account}
      - name: customer_id
        entity: {type: foreign, name: customer}
      - name: balance_date
        granularity: day
        dimension: {type: time}
    derived_semantics:
      entities:
        - name: account_day
          type: primary
          expr: "account_id || '|' || cast(balance_date as varchar)"
    metrics:
      - name: balance_naive             # a plain sum, for comparison
        type: simple
        agg: sum
        expr: balance
      - name: opening_balance           # first day of the period
        type: simple
        agg: sum
        expr: balance
        non_additive_dimension:
          name: balance_date
          window_agg: min
      - name: balance                   # closing balance of the period
        type: simple
        agg: sum
        expr: balance
        non_additive_dimension:
          name: balance_date
          window_agg: max               # max = last day, min = first day
      - name: balance_last_per_account  # each account's own last snapshot
        type: simple
        agg: sum
        expr: balance
        non_additive_dimension:
          name: balance_date
          window_agg: max
          group_by:
            - account

In the legacy format the same settings were called window_choice and window_groupings, inside the measure.

Example

Two accounts with snapshots on the last two days of January and February:

mf query --metrics balance_naive,balance,opening_balance --group-by metric_time__month

metric_time__month      balance_naive    balance    opening_balance
--------------------  ---------------  ---------  -----------------
2026-01-01T00:00:00               330        180                150
2026-02-01T00:00:00               350        190                160

• balance_naive is a plain sum: it adds up every day in the month, a number with no meaning.
• balance keeps only the rows of the last date in each month (window_agg: max) and sums across accounts: 120 + 60 in January.
• opening_balance uses window_agg: min, the first date of the period.

Grouped by day, balance returns the total of each day; grouped by account or customer, each one’s closing balance. The rule only applies along balance_date.

When a snapshot is missing

window_agg: max picks the last date in the period across all rows. If account B has no row on 28 February, the last date is still the 28th, and B disappears from February:

metric_time__month      balance    balance_last_per_account
--------------------  ---------  --------------------------
2026-01-01T00:00:00         180                         180
2026-02-01T00:00:00         150                         220

With group_by: [account] the last date is found per account: A on the 28th (150) and B on the 27th (70). Use it whenever snapshots can be missing or arrive late, which in practice is most of the time.

Average balance per account

agg: average with the same non_additive_dimension and group_by gives the average of the accounts’ closing balances: 90 in January and 95 in February. An average over all daily rows instead would mix days and accounts.

The primary entity

A balance table has no single key column: a row is an account on a day. The example builds the primary entity from both columns with derived_semantics; see how to define entities.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.