Period-to-Date Calculations in Metric Views: A DAX Developer's Guide

In the last post we covered period-over-period growth. This post is about time intelligence: running totals, year-to-date, and trailing windows.

If you’ve written DAX time intelligence functions, you already understand the intent of these calculations. The challenge in DAX is usually the mechanics — filter context, CALCULATE, ALL, DATESYTD. In Metric Views, you declare what you want and the engine handles the rest.

The `window` Block

Window measures extend a standard measure with a window: block. The block controls how the measure aggregates across an ordered dimension — typically a date.

measures:
  - name: Running Total Sales
    expr: SUM(SalesAmount)
    window:
      - order: Order Date   # must match a dimension name exactly
        range: cumulative   # how far the window extends
        semiadditive: last  # behavior when order dim is absent from GROUP BY

Three required fields, every time:

Field	Description
`order`	The dimension that orders the window. Must match a dimension `name` exactly.
`range`	How far the window extends. See options below.
`semiadditive`	`last` or `first` — what value to return when the order dimension is not in the query’s `GROUP BY`.

Version note: Window measures require version: 1.1 or later at the top of the YAML. Features like offset and the inclusive/exclusive modifiers also need 1.1.

Range Options

Range	Behavior
`cumulative`	All rows up to and including the current row
`current`	Only rows matching the current ordering value
`trailing N unit`	N units before the current row, excluding current
`leading N unit`	N units after the current row
`all`	All rows regardless of ordering

Running Total

The simplest window measure: a cumulative sum ordered by date.

DAX:

Running Total Sales =
  CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    FILTER(
      ALL(DimDate[FullDateAlternateKey]),
      DimDate[FullDateAlternateKey] <= MAX(DimDate[FullDateAlternateKey])
    )
  )

Metric View:

- name: Running Total Sales
  expr: SUM(SalesAmount)
  window:
    - order: Order Date
      range: cumulative
      semiadditive: last

Same result. The cumulative range is equivalent to <= MAX(Date) in the DAX filter — all rows up to and including the current date.

Year-to-Date

YTD is a running total that resets at the start of each year. In Metric Views, you achieve this by composing two window specs on a single measure.

DAX:

YTD Sales = TOTALYTD(SUM(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey])

Metric View:

- name: YTD Sales
  expr: SUM(SalesAmount)
  window:
    - order: Order Date
      range: cumulative
      semiadditive: last
    - order: Order Year
      range: current
      semiadditive: last

The first window accumulates across Order Date. The second restricts scope to the current year by filtering to rows where Order Year equals the current row’s value. Together they produce a cumulative sum that resets each January 1st.

Order Year here is an integer dimension (dimdate.CalendarYear) — no need for DATE_TRUNC. Since range: current checks equality, any value that uniquely identifies the year works.

Full definition with joins

CREATE OR REPLACE VIEW adventureworksdw.gold.internet_sales_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: adventureworksdw.silver.factinternetsales
  joins:
    - name: dimdate
      source: adventureworksdw.silver.dimdate
      on: source.OrderDateKey = dimdate.DateKey
  dimensions:
    - name: Order Date
      expr: dimdate.FullDateAlternateKey
    - name: Order Year
      expr: dimdate.CalendarYear
  measures:
    - name: Total Sales
      expr: SUM(SalesAmount)
    - name: YTD Sales
      expr: SUM(SalesAmount)
      window:
        - order: Order Date
          range: cumulative
          semiadditive: last
        - order: Order Year
          range: current
          semiadditive: last
$$

Month-to-Date

MonthNumberOfYear from dimdate is an integer (1–12), but it repeats across years — June 2024 and June 2025 are both 6. To scope the window correctly, add a third window on Order Year so the engine anchors to the right year.

DAX:

MTD Sales = TOTALMTD(SUM(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey])

Metric View:

- name: MTD Sales
  expr: SUM(SalesAmount)
  window:
    - order: Order Date
      range: cumulative
      semiadditive: last
    - order: Order Year
      range: current
      semiadditive: last
    - order: Order Month
      range: current
      semiadditive: last

Where Order Month is:

- name: Order Month
  expr: dimdate.MonthNumberOfYear

The three windows together say: accumulate over date, within the current year, within the current month number.

Quarter-to-Date

Same approach — CalendarQuarter is 1–4 and repeats each year, so pair it with Order Year.

DAX:

QTD Sales = TOTALQTD(SUM(FactInternetSales[SalesAmount]), DimDate[FullDateAlternateKey])

Metric View:

- name: QTD Sales
  expr: SUM(SalesAmount)
  window:
    - order: Order Date
      range: cumulative
      semiadditive: last
    - order: Order Year
      range: current
      semiadditive: last
    - order: Order Quarter
      range: current
      semiadditive: last

Where Order Quarter is:

- name: Order Quarter
  expr: dimdate.CalendarQuarter

All Three Together

Every period-to-date measure follows the same structure: cumulative over date, then one or more current windows to define the period boundary. Here’s a full definition with YTD, QTD, and MTD side by side:

CREATE OR REPLACE VIEW adventureworksdw.gold.internet_sales_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: adventureworksdw.silver.factinternetsales
  joins:
    - name: dimdate
      source: adventureworksdw.silver.dimdate
      on: source.OrderDateKey = dimdate.DateKey
  dimensions:
    - name: Order Date
      expr: dimdate.FullDateAlternateKey
    - name: Order Month
      expr: dimdate.MonthNumberOfYear
    - name: Order Quarter
      expr: dimdate.CalendarQuarter
    - name: Order Year
      expr: dimdate.CalendarYear
  measures:
    - name: Total Sales
      expr: SUM(SalesAmount)
    - name: MTD Sales
      expr: SUM(SalesAmount)
      window:
        - order: Order Date
          range: cumulative
          semiadditive: last
        - order: Order Year
          range: current
          semiadditive: last
        - order: Order Month
          range: current
          semiadditive: last
    - name: QTD Sales
      expr: SUM(SalesAmount)
      window:
        - order: Order Date
          range: cumulative
          semiadditive: last
        - order: Order Year
          range: current
          semiadditive: last
        - order: Order Quarter
          range: current
          semiadditive: last
    - name: YTD Sales
      expr: SUM(SalesAmount)
      window:
        - order: Order Date
          range: cumulative
          semiadditive: last
        - order: Order Year
          range: current
          semiadditive: last
$$

Once you see the pattern, adding a new period is mechanical — define an integer dimension for the boundary, add a current window for it alongside Order Year.

Trailing Windows

Trailing windows are useful for rolling periods — last 30 days, last 12 months, and so on. By default, trailing N unit covers the N units before the current row, excluding the current row. Add inclusive to count the current row too.

DAX (last 30 days):

Trailing 30D Sales =
  CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    DATESINPERIOD(DimDate[FullDateAlternateKey], MAX(DimDate[FullDateAlternateKey]), -30, DAY)
  )

Metric View:

- name: Trailing 30D Sales
  expr: SUM(SalesAmount)
  window:
    - order: Order Date
      range: trailing 29 day inclusive
      semiadditive: last

DATESINPERIOD with -30 days starting from the latest date returns 30 dates, including that date. The equivalent metric view range is trailing 29 day inclusive: the 29 days before today plus today.

Swap the unit and count to get a rolling annual total, with no DAX rewrite needed.

Understanding `semiadditive`

This is the field Power BI developers are most likely to trip over, because DAX handles it implicitly in most time intelligence functions.

Consider querying Running Total Sales without a date in the GROUP BY — say, just by product category. What number should appear? The window has no current date to anchor to, so the engine needs a fallback:

last — use the running total as of the most recent date in scope
first — use the running total as of the earliest date in scope

For running totals and YTD, last is almost always correct. For something like an opening balance, you’d use first.

What’s Next

More posts in this series are on the way.

Terence Brown, Jr.

Period-to-Date Calculations in Metric Views: A DAX Developer's Guide

The `window` Block

Range Options

Running Total

Year-to-Date

Full definition with joins

Month-to-Date

Quarter-to-Date

All Three Together

Trailing Windows

Understanding `semiadditive`

What’s Next

References & Further Reading

Table of Contents

Terence Brown, Jr.

Period-to-Date Calculations in Metric Views: A DAX Developer's Guide

The window Block

Range Options

Running Total

Year-to-Date

Full definition with joins

Month-to-Date

Quarter-to-Date

All Three Together

Trailing Windows

Understanding semiadditive

What’s Next

References & Further Reading

Table of Contents

The `window` Block

Understanding `semiadditive`