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

# Calculating period-over-period changes

> Often, there's a need to calculate a period-over-period change in a metric, e.g., week-over-week or month-over-month growth of clicks, orders, revenue, etc.

## Use case

Often, there's a need to calculate a period-over-period change in a
metric, e.g., week-over-week or month-over-month growth of clicks, orders,
revenue, etc.

## Data modeling

In Cube, calculating a period-over-period metric involves the following
steps:

* Define a [multi-stage measure][ref-multi-stage] for the *current period*.
* Define a [time-shift measure][link-time-shift] that references the current
  period measure and shifts it to the *previous period*.
* Define a [calculated measure][ref-calculated-measure] that references
  these measures and uses them in a calculation, e.g., divides or subtracts them.

<Warning>
  Multi-stage calculations are powered by Tesseract, the [next-generation data modeling
  engine][link-tesseract]. In versions before v1.7.0, it was not enabled by default.
</Warning>

The following data model allows to calculate a month-over-month change of
some value. `current_month_sum` is the base measure, `previous_month_sum`
is a time-shift measure that shifts the current month data to the previous
month, and the `month_over_month_ratio` measure divides their values:

<CodeGroup>
  ```yaml title="YAML" theme={"dark"}
  cubes:
    - name: month_over_month
      sql: |
        SELECT 1 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
        SELECT 2 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
        SELECT 3 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
        SELECT 4 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
        SELECT 5 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
        SELECT 6 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
        SELECT 7 AS value, '2024-04-01'::TIMESTAMP AS date UNION ALL
        SELECT 8 AS value, '2024-04-01'::TIMESTAMP AS date

      dimensions:
        - name: date
          sql: date
          type: time

      measures:
        - name: current_month_sum
          sql: value
          type: sum

        - name: previous_month_sum
          multi_stage: true
          sql: "{current_month_sum}"
          type: number
          time_shift:
            - interval: 1 month
              type: prior

        - name: month_over_month_ratio
          multi_stage: true
          sql: "{current_month_sum} / NULLIF({previous_month_sum}, 0)"
          type: number
  ```

  ```javascript title="JavaScript" theme={"dark"}
  cube(`month_over_month`, {
    sql: `
      SELECT 1 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
      SELECT 2 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
      SELECT 3 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
      SELECT 4 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
      SELECT 5 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
      SELECT 6 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
      SELECT 7 AS value, '2024-04-01'::TIMESTAMP AS date UNION ALL
      SELECT 8 AS value, '2024-04-01'::TIMESTAMP AS date
    `,

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    },

    measures: {
      current_month_sum: {
        sql: `value`,
        type: `sum`
      },

      previous_month_sum: {
        multi_stage: true,
        sql: `${current_month_sum}`,
        type: `number`,
        time_shift: [{
          interval: `1 month`,
          type: `prior`
        }]
      },

      month_over_month_ratio: {
        multi_stage: true,
        sql: `${current_month_sum} / NULLIF(${previous_month_sum}, 0)`,
        type: `number`
      }
    }
  })
  ```
</CodeGroup>

## Result

When querying period-over-period measures, use a time dimension with a
[granularity][ref-time-dimension-granularity] that matches the period — e.g.,
`month` for month-over-month calculations:

<Frame>
  <img src="https://lgo0ecceic.ucarecd.net/2f97cb29-903c-41ff-99e3-295535d2844f/" />
</Frame>

[ref-multi-stage]: /docs/data-modeling/measures#multi-stage-measures

[ref-calculated-measure]: /docs/data-modeling/overview#4-using-calculated-measures

[ref-time-dimension-granularity]: /reference/core-data-apis/rest-api/query-format#time-dimensions-format

[link-tesseract]: https://cube.dev/blog/introducing-next-generation-data-modeling-engine

[link-time-shift]: /docs/data-modeling/measures#time-shift