Are we growing compared to last year?

When would you use this?

The CEO asks for a revenue growth chart showing this year versus last year by month
- and next quarter you want the same chart to automatically include Q3 data.
A regional analyst wants to compare Seoul’s Q2 revenue to Q2 the prior year, without anyone having to manually export and join two date-range queries.
Your executive dashboard needs a YoY percentage change KPI that works whether the user slices by day, month, quarter, or year - without separate metric definitions.
A data team wants to define prior year revenue once as a governed metric so every dashboard uses the same definition rather than each building its own date offset logic.

What this recipe builds

Three metrics using TIME_METRIC with the offset parameter: current-period revenue, prior-year revenue, and a YoY percentage change derived from the two.

This recipe uses the time metrics concept. See the documentation for the full technical reference.

Prerequisites

Same time spine setup as the active customers recipe - date entity marked is_time_spine: yes.

Steps

Revenue (current period)

Revenue anchored to the time spine. This is the baseline for the YoY comparison. It adapts to any grain the user selects.

SQL
YAML

TIME_METRIC(
  order_detail.revenue,
  date_field => order_header.order_date
)

type: metric
entity: order_detail
name: revenue_current_period
display_name: Revenue (current period)
sql: |-
  TIME_METRIC(
    order_detail.revenue,
    date_field => order_header.order_date
  )

Revenue (prior year)

Revenue shifted one year back using offset => '1 year'. Each spine row shows revenue from the same period twelve months earlier - same day, same month, same quarter, depending on the user’s grain.

SQL
YAML

TIME_METRIC(
  order_detail.revenue,
  date_field => order_header.order_date,
  offset => '1 year'
)

type: metric
entity: order_detail
name: revenue_prior_year
display_name: Revenue (prior year)
sql: |-
  TIME_METRIC(
    order_detail.revenue,
    date_field => order_header.order_date,
    offset => '1 year'
  )

Revenue YoY change percentage

Year-over-year growth rate. Positive values mean growth; negative values mean decline.

SQL
YAML

DIV0(
  order_detail.revenue_current_period - order_detail.revenue_prior_year,
  order_detail.revenue_prior_year
)

type: metric
entity: order_detail
name: revenue_yoy_change_pct
display_name: Revenue YoY Change %
sql: |-
  DIV0(
    order_detail.revenue_current_period - order_detail.revenue_prior_year,
    order_detail.revenue_prior_year
  )

DIV0 is Snowflake-native syntax. For other warehouses, replace with NULLIF - for example, (revenue_current_period - revenue_prior_year) / NULLIF(revenue_prior_year, 0).

Sample output

Sliced by month

Each row in revenue_prior_year shows the revenue from the same month one year earlier - January 2022 shows January 2021, February 2022 shows February 2021, and so on.

Month	Current period	Prior year (same month, -1 year)	YoY %
Jan 2022	$84.1M	$78.3M (Jan 2021)	+7.4%
Feb 2022	$76.2M	$71.0M (Feb 2021)	+7.3%
Mar 2022	$89.4M	$81.9M (Mar 2021)	+9.2%

Sliced by week

Each row shows the revenue from the same calendar week one year earlier - week starting Jan 3, 2022 is compared to the week starting Jan 4, 2021.

Week of	Current period	Prior year (same week, -1 year)	YoY %
Jan 3, 2022	$19.8M	$18.4M (Jan 4, 2021)	+7.6%
Jan 10, 2022	$20.3M	$18.9M (Jan 11, 2021)	+7.4%
Jan 17, 2022	$21.1M	$19.5M (Jan 18, 2021)	+8.2%

Time metrics - reference for TIME_METRIC and the offset argument
Active customers & stickiness - introduces TIME_METRIC and the time spine setup

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

When would you use this?

What this recipe builds

Prerequisites

Steps

Sample output

Sliced by month

Sliced by week

​When would you use this?

​What this recipe builds

​Prerequisites

​Steps

​Sample output

​Sliced by month

​Sliced by week

​Related reading

When would you use this?

What this recipe builds

Prerequisites

Steps

Sample output

Sliced by month

Sliced by week

Related reading