Calculating period-over-period changes

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 couple of rolling_window measures with different windows, i.e., one for this period and the other for the previous period.
Define a calculated measure that references these rolling_window measures and uses them in a calculation, e.g., divides or subtracts them.

The following data model allows to calculate a month-over-month change of some value. current_month_sum and previous_month_sum measures define two rolling windows and the month_over_month_ratio measure divides their values:

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
        rolling_window:
          trailing: 1 month
          offset: end
 
      - name: previous_month_sum
        sql: value
        type: sum
        rolling_window:
          trailing: 1 month
          offset: start
 
      - name: month_over_month_ratio
        sql: "{current_month_sum} / {previous_month_sum}"
        type: number

Result

Often, when calculating period-over-period changes, you would also use a query with a time dimension and granularity that matches the period, i.e., month for month-over-month calculations:

{
  "timeDimensions": [
    {
      "dimension": "month_over_month.date",
      "granularity": "month",
      "dateRange": "this year"
    }
  ],
  "measures": [
    "month_over_month.current_month_sum",
    "month_over_month.previous_month_sum",
    "month_over_month.change"
  ]
}

Here's the result:

Calculating nested aggregates Implementing data snapshots