Trying Out Window Measures in Databricks Metric Views

Window measures in Databricks Metric Views went generally available on October 6. They are the piece that was missing for anyone coming from Power BI: year-to-date, year-over-year, rolling windows and closing balances, defined once in Unity Catalog so that every tool querying it gets the same answer.

In the first post of this series I described Metric Views as the same principle as Power BI relationships and DAX measures, defined in Unity Catalog instead of in a BI tool. Time intelligence is where that comparison gets tested, because it is the first thing a Power BI developer writes after SUM.

So I built the patterns from the documentation and queried them the way reports actually do. They work as described. Along the way I collected five things worth knowing before you hand such a measure to other people, mostly about how a measure behaves when it is queried at a different grain than the one it was written for.

How a window measure is defined

A window measure is an ordinary measure with a window block. This is the year-to-date and year-over-year pair on the TPC-H sample orders that every Databricks workspace has:

version: 1.1
source: samples.tpch.orders
dimensions:
  - name: date
    expr: o_orderdate
  - name: month
    expr: DATE_TRUNC('MONTH', date)
  - name: year
    expr: DATE_TRUNC('YEAR', date)
measures:
  - name: total_sales
    expr: SUM(o_totalprice)
  - name: ytd_sales
    expr: SUM(o_totalprice)
    window:
      - order: date
        range: cumulative
        semiadditive: last
      - order: year
        range: current
        semiadditive: last
  - name: sales_py
    expr: SUM(o_totalprice)
    window:
      - order: month
        range: current
        semiadditive: last
        offset: -12 month

Four keys do all the work:

• order names the dimension the window runs along.
• range is how much of that dimension the window covers: current, cumulative, trailing 7 day, leading 2 day, or all.
• offset shifts the window, for example -12 month.
• semiadditive is first or last. It decides which value you get when the order dimension is not in your GROUP BY. This one key is behind most of what follows.

Queried by month, with a filter on month, the results are what you’d expect. Sales in billions:

SELECT month, MEASURE(total_sales), MEASURE(ytd_sales), MEASURE(sales_py)
FROM catalog.schema.sales_metrics
WHERE month >= DATE'1998-05-01'
GROUP BY month ORDER BY month

month     total_sales   ytd_sales   sales_py
1998-05         14.54       71.18      14.63
1998-06         14.11       85.29      14.17
1998-07         14.62       99.91      14.58
1998-08          0.95      100.85      14.60

Two things here are better than I expected. The year-to-date value for May includes January to April, although the filter removed those months. And the prior-year column has values, although the filter removed all of 1997. The query plan shows why: the engine computes the prior year as a second scan of the source and rewrites my filter for it, to month >= 1997-05-01. A filter on the measure’s own order field is moved along with the window.

That is the behaviour a Power BI developer expects from time intelligence, and it is a good starting point for the rest.

Five things I learned along the way

1. Tell the measure about every date field

Same measure, same months. I only added the year, the way every matrix visual with a year and a month on the rows does:

SELECT year, month, MEASURE(total_sales), MEASURE(sales_py)
FROM catalog.schema.sales_metrics
WHERE month >= DATE'1998-05-01'
GROUP BY year, month

year   month     total_sales   sales_py
1998   1998-05         14.54       NULL
1998   1998-06         14.11       NULL
1998   1998-07         14.62       NULL
1998   1998-08          0.95       NULL

Putting the year in the WHERE clause instead (WHERE year = DATE'1998-01-01') does the same thing. year is derived from the same date as month, but the engine treats it as an unrelated dimension: grouped, it splits the window so that 1997 is out of reach; filtered, it removes 1997 before the window is computed.

The fix is to tell the measure that year belongs to the same time axis, by adding it to the window with range: all:

  - name: sales_py
    expr: SUM(o_totalprice)
    window:
      - order: year
        range: all
        semiadditive: last
      - order: month
        range: current
        semiadditive: last
        offset: -12 month

With that, both queries return the correct prior-year values. The documentation shows this pattern in its numeric index example. You need it for every date field a user might put next to the one you ordered by: quarter, fiscal year, week. Forget one and the measure returns NULL for exactly the reports that use it.

2. Every window measure has a grain

sales_py is ordered by month. Group it by year, with the fix from above in place, and you would expect last year’s total. You get one month:

year   total_sales   sales_py
1997        171.86      14.65
1998        100.85      14.60

14.65 billion is December 1996. semiadditive: last means: take the last month in the group, then apply the offset. The 1998 row is August 1997, because the data ends in August 1998.

For last year’s total you need a second measure that runs along year instead:

  - name: sales_py_yearly
    expr: SUM(o_totalprice)
    window:
      - order: year
        range: current
        semiadditive: last
        offset: -1 year

Both side by side, by year:

year   total_sales   sales_py   sales_py_yearly
1996        172.61      14.57            171.63
1997        171.86      14.65            172.61
1998        100.85      14.60            171.86

sales_py_yearly is the full prior year, the same as a plain GROUP BY on the source. By month it does the opposite of sales_py and returns NULL. Now month is the field outside the window, so it separates the groups just like year did in finding 1:

month     total_sales   sales_py   sales_py_yearly
1998-05         14.54      14.63              NULL
1998-06         14.11      14.17              NULL

So it’s one measure per grain: sales_py for months, sales_py_yearly for years. I didn’t find a single definition that follows the grain of the report the way SAMEPERIODLASTYEAR does.

The same thing happens in the other direction. A prior-year measure ordered by date with offset: -1 year is correct per day. Grouped by month it returns the sales of one single day, the last one, 452 million instead of 14.63 billion for May.

The August row in the first table is a related case. The data stops on August 2, 1998, so total_sales is two days and sales_py is all of August 1997: 0.95 against 14.60. A year-over-year percentage on that row reads minus 94 percent.

None of this is a bug. Each measure does what its definition says. The definition includes a grain, and it is up to you to make that grain visible, for example in the name.

3. trailing 7 day leaves out the current day

The documentation’s rolling example counts distinct customers over range: trailing 7 day:

  - name: t7d_customers
    expr: COUNT(DISTINCT o_custkey)
    window:
      - order: date
        range: trailing 7 day
        semiadditive: last

To see which seven days that covers, I put it next to the same measure with inclusive:

date         trailing 7 day   trailing 7 day inclusive
1998-07-31            21216                      21309
1998-08-01            21309                      21301
1998-08-02            21301                      21300

The first column always matches the second column one row earlier. Counting the customers directly in SQL confirms it: the default is exclusive, meaning the seven days before the current day, without the current day. That is a valid definition, but it is not what most people mean by “rolling seven days”. trailing 7 day inclusive gives the seven days ending on the current day.

4. Build a numeric index from the calendar

For fiscal weeks or 4-4-5 periods you can order by an integer column and use a unitless offset such as offset: -1. The documentation says the index must increase by exactly one per period and that this is not validated. I tested what “not validated” looks like, with a small monthly table in which February 2026 has no rows:

month     sales   prev (date)   prev (y*12+m)   prev (row_number)   prev (yyyymm)
2025-12     120           110             110                 110             110
2026-01     130           120             120                 120            NULL
2026-03     150          NULL            NULL                 130            NULL
2026-04     160           150             150                 150             150

• Date order and a calendar index (year * 12 + month) are correct: March has no previous month, so NULL.
• A row_number index reports January as the month before March.
• A yyyymm index loses the previous month every January, because 202601 minus 1 is not 202512.

The offset is arithmetic on the value, not a step to the previous row. So build the index from the calendar, never from the data.

5. Closing balances use the last date in view

A closing balance is range: current with semiadditive: last: within the period, take the last date. Three accounts, month-end balances. Account C has no row in March, account B has none in February:

account   closing_balance
A                     120
B                     220
C                     310

(grand total)         340

Each account row uses that account’s own last date, so C shows its February balance. The total uses the last date overall, March 31, where only A and B have rows. The rows sum to 650 and the total says 340.

Whether 340 or 650 is right depends on whether C was closed or just not loaded. The measure cannot know, and there is no option for “last date that has a value, per account”. If your snapshot table can have gaps, fill them before the metric view sees it.

Coming from DAX

To see which of these are Metric View behaviour and which are just time intelligence, I rebuilt the same measures in a small Power BI import model with a marked date table and asked the same questions.

The real difference is the date table. SAMEPERIODLASTYEAR returned correct values for the queries from findings 1 and 2, because Power BI knows that year, month and day are one axis. In a Metric View, every date field is independent until you list it in the window.

Two of the findings aren’t Metric View topics at all. DAX also compared the two days of the last month with a full prior-year month, and LASTDATE on the fact table gives the same 340 for the closing balance. Those are properties of time intelligence, whichever tool computes it.

Getting it into Power BI

Databricks recommends a native query with MEASURE() in DirectQuery, or a wrapper view that Power BI can import as a normal table:

CREATE VIEW catalog.schema.sales_for_powerbi AS
SELECT month, priority,
       MEASURE(total_sales) AS total_sales,
       MEASURE(ytd_sales) AS ytd_sales,
       MEASURE(sales_py) AS sales_py
FROM catalog.schema.sales_metrics
GROUP BY ALL

The wrapper fixes the grain, and from there Power BI re-aggregates on its own. Sums survive that. Year-to-date doesn’t: summed over the months of 1998 it comes to 499 billion for a year that sold 101, and a distinct count breaks as soon as any dimension is dropped. MEASURE() can’t be called on the wrapper, so in practice that means one wrapper per grain, or keeping the non-additive measures in DAX as well.

A short note for readers of the Apache Ossie series: Ossie’s Databricks converter carries window blocks through a round trip unchanged, as a vendor extension. Its Power BI converter drops them with a warning, so a running total arrives in Power BI as a plain SUM.

What this adds up to

I came away liking window measures. Year-to-date, rolling windows and prior-period comparisons in plain SQL are long, easy to get wrong, and different in every report. Here they are five lines of YAML, and the filter handling on the order field is better than I expected.

The main difference to Power BI is the date table. In a Power BI model it knows that year, quarter, month and day are one axis. In a Metric View you state that yourself, per measure, in the window block. Once you know that, most of what I ran into above is a line or two of YAML.

So this is the checklist I would use when building these for other people to query:

• List every related date field in the window with range: all.
• Build one measure per grain and put the grain in the name: sales_py_monthly and sales_py_yearly, not just sales_py.
• Write inclusive when you mean a rolling window that includes the current day.
• Build numeric indexes from the calendar.
• Fill gaps in snapshot tables before the Metric View.
• Query each measure one level above and one level below the grain you built it for. The monthly sales_py, for example, returns a single month by year and NULL by day.