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.

This recipe compares against a single fixed interval. To let a data consumer choose the interval (and the rolling window) at query time, see Configurable rolling windows.

Data modeling

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

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

Multi-stage calculations are powered by Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default.

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:

YAML

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

Result

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