Window measures on a numeric index column in metric views

Use cases

Imagine you need to calculate trends or comparisons across periods that do not follow traditional calendars—such as fiscal weeks, ISO weeks, or 4-4-5 accounting periods. With this update, window measures in metric views now support unitless numeric range and offset parameters on integer index columns.

This allows you to:

• Calculate moving averages or running totals using sequential integer positions.
• Perform period comparisons using numeric indices instead of calendar dates.
• Define flexible windows based on dense numeric sequences.

How to use

You configure the window measure by pointing to an integer index column and applying a numeric range or offset, ensuring dependency columns use the range: all specification.

version: 1.1
source: main.default.financial_data
fields:
  - name: fiscal_year
    expr: fiscal_year
  - name: fiscal_period
    expr: fiscal_period
  - name: period_index
    expr: fiscal_year * 12 + fiscal_period
    window:
      - order: period_index
        range: all
measures:
  - name: rolling_sales
    expr: SUM(sales)
    window:
      - order: period_index
        range: trailing 3
        semiadditive: last

What happens in this code:

1. The specified YAML version is 1.1.
2. The data source points to a financial data table.
3. The period_index field is derived by combining fiscal year and period, with range: all to prevent improper partitioning.
4. The rolling_sales measure sums sales using a trailing window of 3 previous integer positions.

Limitations and requirements

• Requires Databricks Runtime 19 or above and YAML specification version 1.1 or above.
• The index column must be integral (TINYINT, SMALLINT, INT, or BIGINT), monotonic, and consecutive (dense) without gaps or repeats.