Derived metrics

In MetricFlow, derived metrics are metrics created by defining an expression using other metrics. They enable you to perform calculations with existing metrics. This is helpful for combining metrics and doing math functions on aggregated columns, like creating a profit metric.

The parameters, description, and type for derived metrics are:

(Applies to dbt v1.12 and later)

Parameter	Description	Required	Type
name	The name of the metric.	Required	String
description	A human-readable summary of the metric.	Optional	String
type	The metric type (simple, cumulative, ratio, derived, or conversion).	Required	String
label	Display name for downstream tools. Accepts plain text, spaces, and quotes (such as orders_total or "orders_total").	Optional	String
expr	The expression that combines other metrics. Validation warns if it is missing or references undefined metrics.	Required	String
input_metrics	Defines aliases, filters, or offsets for metrics referenced in the expression. Needed only when you customize those attributes.	Optional	List
input_metrics.name	The name of the referenced metric defined elsewhere in the project.	Required when metric_aliases provided	String
input_metrics.alias	Alternate name you can reference in expr.	Optional	String
input_metrics.filter	Filter to apply to the referenced metric.	Optional	String
input_metrics.offset_window	Offset applied to the referenced metric (for example, 1 week). Allowed only for derived metrics.	Optional	String

The following displays the complete specification for derived metrics, along with an example.

(Applies to dbt v1.12 and later)

models/model.yml

metrics:
  - name: my_derived_metric
    description: cool derived metric # Optional
    label: my derived metric label # Optional
    type: derived # Required 
    expr: my_simple_metric - my_simple_metric_a_week_ago # Required for derived
    input_metrics: # Required for derived if using aliases / filters / offset_window for portions of the expression
      - name: my_simple_metric
        alias: my_simple_metric_a_week_ago
        filter: "{{ Dimension('my_primary_entity__my_categorical_dimension_column') }} > 10"
        offset_window: 1 week # Allowed for derived metrics

For advanced data modeling, you can use fill_nulls_with and join_to_timespine to set null metric values to zero, ensuring numeric values for every data row.

Derived metrics example

(Applies to dbt v1.12 and later)

models/model.yml

models:
  - name: fct_orders
    semantic_model:
      enabled: true
      name: order
    ... rest of config ...
    metrics:
      - name: order_gross_profit
        description: "Gross profit from each order."
        label: Order gross profit
        type: derived
        expr: revenue - cost
        input_metrics:
          - name: order_total
            alias: revenue
          - name: order_cost
            alias: cost
      - name: food_order_gross_profit
        label: Food order gross profit
        description: "The gross profit for each food order."
        type: derived
        expr: revenue - cost
        input_metrics:
          - name: order_total
            alias: revenue
            filter: |
              {{ Dimension('order__is_food_order') }} = True
          - name: order_cost
            alias: cost
            filter: |
              {{ Dimension('order__is_food_order') }} = True
      - name: order_total_growth_mom
        description: "Percentage growth of orders total compared to 1 month ago"
        label: Order total growth % M/M
        type: derived
        expr: (order_total - order_total_prev_month) * 100 / order_total_prev_month
        input_metrics:
          - name: order_total
          - name: order_total
            alias: order_total_prev_month
            offset_window: 1 month

Derived metric offset

To perform calculations using a metric's value from a previous time period, you can add an offset parameter to a derived metric. For example, if you want to calculate period-over-period growth or track user retention, you can use this metric offset.

Note: You must include the metric_time dimension when querying a derived metric with an offset window.

The following example displays how you can calculate monthly revenue growth using a 1-month offset window:

(Applies to dbt v1.12 and later)

models/model.yml

models:
  - name: customers
    semantic_model:
      enabled: true
      name: customers_semantic_model
    metrics:
      - name: customer_retention
        description: Percentage of customers that are active now and those active 1 month ago
        label: customer_retention
        type: derived
        expr: current_active_customers / active_customers_prev_month
        input_metrics:
          - name: active_customers
            alias: current_active_customers
          - name: active_customers
            alias: active_customers_prev_month
            offset_window: 1 month

Offset windows and granularity

You can query any granularity and offset window combination. The following example queries a metric with a 7-day offset and a monthly grain:

(Applies to dbt v1.12 and later)

models/model.yml

models:
  - name: customers
    semantic_model:
      enabled: true
      name: customers_semantic_model
    ... rest of config ...
    metrics:
      - name: d7_booking_change
        description: Difference between bookings now and 7 days ago
        type: derived
        label: d7 bookings change
        expr: current_bookings - bookings_7_days_ago
        input_metrics:
          - name: bookings
            alias: current_bookings
          - name: bookings
            offset_window: 7 days
            alias: bookings_7_days_ago

When you run the query dbt sl query --metrics d7_booking_change --group-by metric_time__month for the metric, here's how it's calculated. For dbt Core, you can use the mf query prefix.

1. Retrieve the raw, unaggregated dataset with the specified (Applies to dbt v1.12 and later) simple metric and dimensions at the smallest level of detail, which is currently 'day'.
2. Then, perform an offset join on the daily dataset, followed by performing a date trunc and aggregation to the requested granularity. For example, to calculate d7_booking_change for July 2017:
   • First, sum up all the booking values for each day in July to calculate the bookings metric.
   • The following table displays the range of days that make up this monthly aggregation.

	Orders	Metric_time
	330	2017-07-31
	7030	2017-07-30 to 2017-07-02
	78	2017-07-01
Total	7438	2017-07-01

3. Calculate July's bookings with a 7-day offset. The following table displays the range of days that make up this monthly aggregation. Note that the month begins 7 days later (offset by 7 days) on 2017-07-24.

	Orders	Metric_time
	329	2017-07-24
	6840	2017-07-23 to 2017-06-30
	83	2017-06-24
Total	7252	2017-07-01

4. Lastly, calculate the derived metric and return the final result set:

bookings - bookings_7_days_ago would be compile as 7438 - 7252 = 186. 

d7_booking_change	metric_time__month
186	2017-07-01

Related docs

• Fill null values for simple, derived, or ratio metrics

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.