Reusing period-to-date logic across measures

Use case

Period-to-date metrics — week-to-date (WTD), month-to-date (MTD), quarter-to-date (QTD), and year-to-date (YTD) — multiply quickly. A model with 100 base measures and four periods needs 400 measure definitions, and each one repeats the same window logic. Adding a fifth period means touching every cube. The period logic itself does not vary. Only the base measure, its aggregation type, and the period change. That makes it a good fit for a Jinja macro: define the shape once, then call it per measure.

This recipe generates measures at compile time, so the model still contains one member per (measure × period) pair. What it removes is the duplication in the source files. To let a data consumer choose the period at query time instead, see Configurable rolling windows.

Data modeling

Period-to-date is a built-in rolling window type. A single measure looks like this:

measures:
  - name: revenue_year_to_date
    sql: amount
    type: sum
    rolling_window:
      type: to_date
      granularity: year

Defining the macro

Put the macro in a .jinja file under model/macros so that every cube can import it:

model/macros/period_to_date.jinja

{%- macro to_date_measures(name, sql='', type='sum', periods=['week', 'month', 'quarter', 'year']) -%}
{%- for period in periods %}
      - name: {{ name | safe }}_{{ period | safe }}_to_date
        description: {{ name | safe }}, accumulated from the start of the {{ period | safe }}
        {% if sql %}sql: |-
          {{ sql | indent(10) | safe }}
        {% endif %}type: {{ type | safe }}
        rolling_window:
          type: to_date
          granularity: {{ period | safe }}
{% endfor -%}
{%- endmacro -%}

The safe filters and the |- block scalar above are load-bearing. Every value that is concatenated with other text needs safe — the macro applies it throughout for consistency — and a SQL argument has to be emitted as a literal string with indent applied before safe — see escaping unsafe strings. Getting either wrong surfaces as a YAML parse error far from the macro that caused it.The macro also emits its own leading indentation. Indenting the {{ ... }} call in the cube file instead only indents the first generated line.

Applying it to a cube

Import the macro and call it once per base measure:

model/cubes/orders.yml

{%- import "macros/period_to_date.jinja" as ptd -%}

cubes:
  - name: orders
    sql_table: orders

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: created_at
        sql: created_at
        type: time

    measures:
      - name: revenue
        sql: amount
        type: sum

      - name: count
        type: count
{{ ptd.to_date_measures("revenue", "amount") }}
{{ ptd.to_date_measures("count", type="count") }}

Two calls generate eight measures: revenue_week_to_date through revenue_year_to_date, and the same four for count. Adding a period is a one-token change to the macro’s default list, not an edit to every cube. sql is optional so that a count measure stays a COUNT(*), matching its base measure. Passing a column to a count makes it a COUNT(<column>) instead, which skips rows where that column is null.

Overriding the periods

The periods parameter defaults to all four periods. Pass a list to generate a subset, which keeps the model free of members nobody queries:

model/cubes/customer_signups.yml

{%- import "macros/period_to_date.jinja" as ptd -%}

cubes:
  - name: customer_signups
    sql_table: signups

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: created_at
        sql: created_at
        type: time

    measures:
      - name: signups
        type: count
{{ ptd.to_date_measures("signups", type="count", periods=["month", "year"]) }}

Result

Querying the generated measures by day shows each window accumulating from the start of its own period and resetting at the boundary. The columns below are revenue_week_to_date, revenue_month_to_date, revenue_quarter_to_date, and revenue_year_to_date. The orders cube holds 30 in revenue on 30 December 2024 and nothing earlier. 1 January falls in the ISO week that began on 30 December, so WTD opens with that 30 already counted, while MTD, QTD, and YTD all start fresh:

created_at	revenue	WTD	MTD	QTD	YTD
2025-01-01	100	130	100	100	100
2025-01-02	200	330	300	300	300
2025-01-05	400	730	700	700	700
2025-01-06	800	800	1500	1500	1500
2025-01-07	1600	2400	3100	3100	3100

On 6 January, a Monday, WTD resets to that day’s own revenue while the longer windows carry on. Grouping by month shows the longer windows one level up. January starts a new quarter and a new year, so both QTD and YTD drop December’s 30 and restart; February then accumulates on top of January in both:

created_at	QTD	YTD
2024-12	30	30
2025-01	3100	3100
2025-02	8100	8100

MTD is omitted here on purpose: grouping a to_date window by its own granularity collapses it onto the base measure, so the MTD column would just repeat each month’s revenue. Group by a finer granularity than the window to see it accumulate.

Following a fiscal or retail calendar

The macro needs no change to follow a fiscal or retail calendar. A calendar cube overrides what week, month, quarter, and year mean, and the generated measures pick that up unchanged, because they already emit those names as their granularity. See custom calendars for the calendar cube itself, how a to_date window behaves over it, and its pre-aggregation requirements.

Rolling windows over calendar granularities require Tesseract, the next-generation data modeling engine. In versions before v1.7.0, it was not enabled by default. Querying a to_date window over an overridden granularity also requires v1.7.32 or later.

What the macro adds is the period list. Pass only the periods the calendar cube actually overrides. A calendar cube that defines week, month, and year but not quarter still accepts granularity: quarter — it falls back to DATE_TRUNC and reports Gregorian quarters next to retail months, with no error. Restrict the list to match:

{{ ptd.to_date_measures("revenue", "amount", periods=["week", "month", "year"]) }}

Group by the time dimension that carries the override. The retail periods come from the calendar cube’s overridden dimension. A query that groups by a dimension without the override falls back to DATE_TRUNC and returns Gregorian periods, with no error.

Do not pass a name like retail_month. A variable-length period is defined with sql, and a granularity defined that way must be named after a default granularity, so retail_month does not compile. A fixed-length period is different: a custom granularity defined with interval does carry a name of your own, and the macro takes it like any other period. This too requires Tesseract — on the legacy planner a custom granularity name in a to_date window compiles but fails at query time. A name of your own also has to be declared under granularities on the very time dimension the query groups by. Unlike a default name, it has nothing to fall back to, so the query fails outright rather than silently reporting Gregorian periods.

{{ ptd.to_date_measures("revenue", "amount", periods=["fiscal_year"]) }}

​Use case

​Data modeling

​Defining the macro

​Applying it to a cube

​Overriding the periods

​Result

​Following a fiscal or retail calendar

Use case

Data modeling

Defining the macro

Applying it to a cube

Overriding the periods

Result

Following a fiscal or retail calendar