# 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 ```yaml 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](./fill-nulls-advanced.md), ensuring numeric values for every data row. ## Derived metrics example (Applies to dbt v1.12 and later) models/model.yml ```yaml 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](./dimensions.md#time) 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 ```yaml 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 ```yaml 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: ```bash 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](./fill-nulls-advanced.md) ## Was this page helpful? YesNo [Privacy policy](https://www.getdbt.com/cloud/privacy-policy)[Create a GitHub issue](https://github.com/dbt-labs/docs.getdbt.com/issues) This site is protected by reCAPTCHA and the Google [Privacy Policy](https://policies.google.com/privacy) and [Terms of Service](https://policies.google.com/terms) apply.