> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Are we growing compared to last year?

> Compare revenue to the same period last year and compute the growth rate

## 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.

<Tip>
  This recipe uses the [time metrics](/docs/advanced-modeling/time-metrics) concept.
  See the documentation for the full technical reference.
</Tip>

## Prerequisites

Same time spine setup as the [active customers recipe](/docs/recipes/active-customers) -
`date` entity marked `is_time_spine: yes`.

## Steps

<Steps>
  <Step title="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.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        TIME_METRIC(
          order_detail.revenue,
          date_field => order_header.order_date
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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
          )
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="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.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        TIME_METRIC(
          order_detail.revenue,
          date_field => order_header.order_date,
          offset => '1 year'
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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'
          )
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="Revenue YoY change percentage">
    Year-over-year growth rate. Positive values mean growth; negative values mean
    decline.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        DIV0(
          order_detail.revenue_current_period - order_detail.revenue_prior_year,
          order_detail.revenue_prior_year
        )
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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
          )
        ```
      </Tab>
    </Tabs>

    <Tip>
      `DIV0` is Snowflake-native syntax. For other warehouses, replace with
      `NULLIF` - for example,
      `(revenue_current_period - revenue_prior_year) / NULLIF(revenue_prior_year, 0)`.
    </Tip>
  </Step>
</Steps>

## 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% |

## Related reading

* [Time metrics](/docs/advanced-modeling/time-metrics) - reference for `TIME_METRIC` and
  the `offset` argument
* [Active customers & stickiness](/docs/recipes/active-customers) - introduces
  `TIME_METRIC` and the time spine setup