Measures

A measure is a queryable number defined on a semantic model. Measures unify simple aggregations (sums, counts, distinct counts) and derived KPIs (ratios, growth rates) into a single concept — there is no separate "metric". Measure names are unique per tenant, so you reference a measure by its bare name and the layer resolves which model backs it.

Two types

`measure_type`	What it is
`aggregate`	Applies an aggregation function to a column or expression. Also carries cumulative windows and semi-additive behavior.
`calculated`	An arithmetic expression over other measures (ratios, growth rates). Has no `agg` of its own.

Shared required fields: name, measure_type, description. label is optional.

Aggregate measures

measures:
  - name: order_total
    measure_type: aggregate
    agg: sum
    sql: order_amount
    description: Sum of order amounts.
  - name: distinct_buyers
    measure_type: aggregate
    agg: count_distinct
    sql: user_id
    description: Distinct users who placed an order.

Field	Required	Notes
`agg`	Yes (aggregate)	One of `sum`, `count`, `count_distinct`, `count_distinct_approx`, `hll_sketch`, `avg`, `min`, `max`, `median`, `percentile`, `stddev`, `variance`.
`sql`	No*	Column or SQL expression to aggregate. Defaults to the measure `name` when omitted. *Required for `hll_sketch` and `count_distinct_approx`.
`filter`	No	A SQL predicate applied before aggregation, e.g. `status = 'completed'`. Plain SQL only (no Jinja, no subqueries). Use dotted `model.dimension` to filter on a joined model.
`override_time_dimension`	No	Aggregate this measure over a different time dimension than the model's `default_time_dimension`.

Filtered variants

- name: completed_revenue
  measure_type: aggregate
  agg: sum
  sql: order_amount
  filter: status = 'completed'
  description: Revenue from completed orders only.

Cumulative measures

Still measure_type: aggregate — these are modifiers, not a separate type. grain_to_date and rolling_window are mutually exclusive.

Field	Notes
`grain_to_date`	Accumulates from the start of the named period. One of `day`, `week`, `month`, `quarter`, `year` (e.g. `month` → month-to-date).
`rolling_window`	Trailing window, format `"<N> <unit>"` where unit is `days`, `weeks`, `months`, `quarters`, or `years` (e.g. `"7 days"`).

- name: mtd_revenue
  measure_type: aggregate
  agg: sum
  sql: order_amount
  grain_to_date: month
  description: Month-to-date revenue.

- name: rolling_7d_buyers
  measure_type: aggregate
  agg: count_distinct
  sql: user_id
  rolling_window: "7 days"
  description: Distinct buyers in the trailing 7-day window.

Semi-additive (snapshot) measures

A measure is semi-additive when the same entity appears in multiple rows over time (daily balances, subscriber snapshots, rolling active-user tables), so summing across time would double-count. Declare it with:

Field	Notes
`non_additive_dim`	The time dimension the measure must not be summed across. Must name a `time` dimension on the same model.
`non_additive_window`	`max` keeps rows at the latest value of that dimension (period-end / as-of-now — the common case); `min` keeps the earliest. Only `max` and `min` are valid.
`non_additive_groupings`	Optional entity columns to partition by when collapsing (e.g. `[account_id]`).

- name: account_balance
  measure_type: aggregate
  agg: sum
  sql: balance
  non_additive_dim: snapshot_date
  non_additive_window: max
  non_additive_groupings: [account_id]
  description: Latest balance per account, summable across accounts.

Valid with sum / avg-style aggregations, not with count. If a count double-counts, switch it to count_distinct, which deduplicates natively and needs no non_additive_dim.

Calculated measures

A calculated measure computes a value from other measures via derived_sql, which is required for measure_type: calculated (and it takes no agg of its own). Reference other measures by their bare names — or dotted model.measure for an explicit cross-model reference — and guard divisions with NULLIF; there is no automatic null protection. When an operand needs to be named explicitly or shifted in time, declare it with inputs and operand_offsets.

- name: avg_order_value
  measure_type: calculated
  derived_sql: order_total / NULLIF(order_count, 0)
  description: Average revenue per order.

Cross-model calculations join via the shared-dimension mechanism described in Semantic model → Joins:

- name: margin
  measure_type: calculated
  derived_sql: orders.revenue / NULLIF(inventory.cost, 0)
  description: Revenue divided by cost of goods.

Inputs and time-shifted operands

When a calculated measure combines operands that are offset in time, name each operand with inputs and shift it with operand_offsets:

Field	Notes
`inputs[].alias`	Name used in `derived_sql` for this operand.
`inputs[].measure`	The measure to bind — a bare name (no dots).
`inputs[].filter`	Optional filter applied to that operand.
`operand_offsets.<measure>.value` / `.unit` / `.direction`	Shift an operand by `value` × `unit` (`day`…`year`), `direction` `lag` (backward) or `lead` (forward). The three go together.
`operand_offsets.<measure>.to_grain`	Optionally align the join key to a coarser grain first.

- name: completion_ratio
  measure_type: calculated
  derived_sql: completed / NULLIF(started, 0)
  operand_offsets:
    started:
      value: 7
      unit: day
      direction: lag
  description: Completed relative to started, with the started operand shifted back 7 days.

Pre-aggregation note

Aggregate measures can be accelerated with pre-aggregations. Calculated measures are not pre-agg-eligible — pre-aggregate their component measures and the calculation is derived from them. count_distinct and rolling-window measures are not re-aggregatable across grains, so each queried grain needs its own pre-aggregation.

Still have questions?

Write to support@sundial.so

On this page