Time intelligence (declarative YoY, PoP, YTD, rolling)

Mondrian-4 supports a <TimeCalc> schema element that declares common time-intelligence metrics. The schema loader desugars each declaration into a validated <CalculatedMember> on [Measures] — so you state what you want rather than hand-writing and maintaining MDX formulas by hand.

Why declarative time intelligence?

Without <TimeCalc>, year-over-year growth requires a calculated member like:

<CalculatedMember name="Revenue YoY" dimension="Measures">
  <Formula>
    ([Measures].[Revenue] - (ParallelPeriod([Calendar].[Year], 1, [Calendar].CurrentMember),
     [Measures].[Revenue]))
    / (ParallelPeriod([Calendar].[Year], 1, [Calendar].CurrentMember),
       [Measures].[Revenue])
  </Formula>
  <CalculatedMemberProperty name="FORMAT_STRING" value="0.0%"/>
</CalculatedMember>

With <TimeCalc> the same metric is:

<TimeCalc name="Revenue YoY" type="yoy" measure="Revenue"
          timeDimension="Calendar" formatString="0.0%"/>

The loader generates the MDX for you, validates that the referenced measure and time dimension exist, and throws a load-time error rather than producing a silently wrong result.

Prerequisite: a typed Time dimension

<TimeCalc> requires the cube to have a typed Time dimension — a <Dimension> with type="TIME" whose hierarchy has named levels for year, quarter, and month. The year level must carry levelType="TimeYears", and the quarter and month levels must carry levelType="TimeQuarters" and levelType="TimeMonths" respectively. The within-year calculations (ytd, pop, rolling) require at minimum a month-level in the hierarchy.

A minimal Calendar dimension that satisfies the requirement:

XML

<Dimension name="Calendar" type="TIME" table="dim_date" key="Date">
  <Attributes>
    <Attribute name="Year"    keyColumn="year_num"    levelType="TimeYears"/>
    <Attribute name="Quarter" keyColumn="quarter_key" levelType="TimeQuarters"/>
    <Attribute name="Month"   keyColumn="month_key"   levelType="TimeMonths"/>
    <Attribute name="Date"    keyColumn="date_key"    levelType="TimeDays"/>
  </Attributes>
  <Hierarchies>
    <Hierarchy name="Calendar" allMemberName="All Time">
      <Level attribute="Year"/>
      <Level attribute="Quarter"/>
      <Level attribute="Month"/>
      <Level attribute="Date"/>
    </Hierarchy>
  </Hierarchies>
</Dimension>

YAML

Calendar:
  type: "TIME"
  table: "dim_date"
  key: "Date"
  attributes:
  - name: "Year"
    key_column: "year_num"
    level_type: "TimeYears"
  - name: "Quarter"
    key_column: "quarter_key"
    level_type: "TimeQuarters"
  - name: "Month"
    key_column: "month_key"
    level_type: "TimeMonths"
  - name: "Date"
    key_column: "date_key"
    level_type: "TimeDays"
  hierarchies:
  - name: "Calendar"
    all_member_name: "All Time"
    levels:
    - attribute: "Year"
    - attribute: "Quarter"
    - attribute: "Month"
    - attribute: "Date"

Schema placement

<TimeCalc> elements are wrapped in a <TimeCalcs> block inside a <Cube>, at the same level as <CalculatedMembers>:

XML

<Cube name="Monthly Revenue">

  <Dimensions>
    <Dimension source="Calendar"/>
    <!-- other dimensions -->
  </Dimensions>

  <MeasureGroups>
    <MeasureGroup name="Revenue" table="monthly_revenue_fact">
      <Measures>
        <Measure name="Revenue" column="revenue" aggregator="sum"/>
      </Measures>
      <DimensionLinks>
        <ForeignKeyLink dimension="Calendar" foreignKeyColumn="month_key"/>
      </DimensionLinks>
    </MeasureGroup>
  </MeasureGroups>

  <TimeCalcs>
    <TimeCalc name="Revenue YoY" type="yoy"     measure="Revenue" timeDimension="Calendar" formatString="0.0%"/>
    <TimeCalc name="Revenue PoP" type="pop"     measure="Revenue" timeDimension="Calendar" formatString="0.0%"/>
    <TimeCalc name="Revenue YTD" type="ytd"     measure="Revenue" timeDimension="Calendar"/>
    <TimeCalc name="Revenue R3"  type="rolling" measure="Revenue" timeDimension="Calendar" window="3" function="avg"/>
  </TimeCalcs>

</Cube>

YAML

Monthly Revenue:
  dimensions:
  - source: "Calendar"
  measure_groups:
  - name: "Revenue"
    table: "monthly_revenue_fact"
    measures:
    - name: "Revenue"
      column: "revenue"
      aggregator: "sum"
    dimension_links:
    - type: "foreign_key"
      dimension: "Calendar"
      foreign_key_column: "month_key"
  time_calcs:
  - name: "Revenue YoY"
    type: "yoy"
    measure: "Revenue"
    time_dimension: "Calendar"
    format_string: "0.0%"
  - name: "Revenue PoP"
    type: "pop"
    measure: "Revenue"
    time_dimension: "Calendar"
    format_string: "0.0%"
  - name: "Revenue YTD"
    type: "ytd"
    measure: "Revenue"
    time_dimension: "Calendar"
  - name: "Revenue R3"
    type: "rolling"
    measure: "Revenue"
    time_dimension: "Calendar"
    window: 3
    function: "avg"

Attribute reference

Attribute	XML / YAML key	Required	Description
name	name	yes	The generated calculated member name. Appears in [Measures] just like any other measure.
type	type	yes	The metric type: yoy, pop, ytd, or rolling. See Metric types below.
measure	measure	yes	The name of an existing <Measure> in the cube. The loader rejects an unknown measure at schema load.
timeDimension	time_dimension	conditional	The name of a type="TIME" dimension. May be omitted when the cube has exactly one TIME dimension; required when it has more than one.
window	window	rolling only	Integer number of periods to include in the rolling window.
function	function	rolling only	Aggregation function over the window: sum (default) or avg.
formatString	format_string	no	MDX format string applied to the generated member, e.g. "0.0%" or "#,###".

Metric types

yoy — year-over-year growth

Reports the percentage change compared to the same period in the prior year.

Formula shape:

([Measures].[<measure>] - ([Measures].[<measure>], ParallelPeriod(<YearLevel>, 1)))
/ ([Measures].[<measure>], ParallelPeriod(<YearLevel>, 1))

ParallelPeriod is called with just the year level and a lag of 1 — it takes the current member from context rather than being handed one. It navigates to the same relative position a year back using the TimeYears level, and the result is NULL for the first full year of data, where there is no prior year to compare against.

pop — period-over-period growth

Reports the percentage change compared to the immediately preceding period (the period just before the current one at the same level).

Formula shape:

([Measures].[<measure>] - ([Measures].[<measure>], <hierarchy>.CurrentMember.PrevMember))
/ ([Measures].[<measure>], <hierarchy>.CurrentMember.PrevMember)

.PrevMember is a property on a member, not a function taking one — <hierarchy>.CurrentMember.PrevMember, never PrevMember(...). It steps back one position in the hierarchy’s natural ordering, and the result is NULL for the very first member of a level, which has no predecessor.

ytd — year-to-date cumulative

Reports the cumulative value of the measure from the start of the current year through the current period.

Formula shape:

Aggregate(Ytd(<hierarchy>.CurrentMember), [Measures].[<measure>])

Ytd() returns the set of all periods from the first period of the current year through the current period. Aggregate applies the measure’s native aggregation (typically sum) over that set.

rolling — rolling window

Reports the aggregate of the measure over the last window periods, using sum or avg.

Formula shape (avg, window=3):

Avg(LastPeriods(3, <hierarchy>.CurrentMember), [Measures].[<measure>])

Formula shape (the default, window=N):

Aggregate(LastPeriods(N, <hierarchy>.CurrentMember), [Measures].[<measure>])

Note it’s Aggregate, not Sum. That’s deliberate: Aggregate applies the measure’s own declared aggregator, so a rolling window over an avg or distinct-count measure stays correct instead of being silently summed.

LastPeriods(N, member) returns the set of the N periods ending at the current member. If fewer than N periods are available (e.g. early in the data history), the window shrinks to however many periods exist — it does not pad with zeros.

Validation behaviour

The loader is fail-closed: schema load is aborted with a clear error message if any of the following conditions are detected.

Condition	Error
measure names a member that doesn’t exist in the cube	TimeCalc 'X': measure 'Y' not found in cube 'C'
No TIME dimension with a TimeYears level can be resolved — whether because the cube has none, or because the named one doesn’t qualify	TimeCalc 'X': no Time dimension [named 'Y'] with a TimeYears level in cube 'C'
type="rolling" without a window	TimeCalc type='rolling' requires a 'window'

Note the resolution failure is one error, not several: the loader looks for a TIME dimension carrying a TimeYears level, and reports the same message whether you named one that doesn’t qualify or left timeDimension off entirely. If you named one, it appears in the message — which is usually enough to tell the two cases apart.

There is no silent wrong result — every misconfiguration is caught before the first query runs.

Note

<TimeCalc> works on the Calcite query backend (mondrian.backend=calcite), which is the Saiku default. The legacy SQL generator does not support the generated MDX patterns.

Worked example: Bank demo monthly revenue

The Bank demo ships a Monthly Revenue cube over a monthly revenue series. The raw data for two years:

Year	Month	Revenue
2024	Jan	100
2024	Feb	200
2024	Mar	300
2025	Jan	150
2025	Feb	250
2025	Mar	350

The cube declares all four <TimeCalc> types against the Calendar dimension (Year > Quarter > Month):

XML

<TimeCalcs>
  <TimeCalc name="Revenue YoY" type="yoy"     measure="Revenue" timeDimension="Calendar" formatString="0.0%"/>
  <TimeCalc name="Revenue PoP" type="pop"     measure="Revenue" timeDimension="Calendar" formatString="0.0%"/>
  <TimeCalc name="Revenue YTD" type="ytd"     measure="Revenue" timeDimension="Calendar"/>
  <TimeCalc name="Revenue R3"  type="rolling" measure="Revenue" timeDimension="Calendar" window="3" function="avg"/>
</TimeCalcs>

YAML

time_calcs:
- name: "Revenue YoY"
  type: "yoy"
  measure: "Revenue"
  time_dimension: "Calendar"
  format_string: "0.0%"
- name: "Revenue PoP"
  type: "pop"
  measure: "Revenue"
  time_dimension: "Calendar"
  format_string: "0.0%"
- name: "Revenue YTD"
  type: "ytd"
  measure: "Revenue"
  time_dimension: "Calendar"
- name: "Revenue R3"
  type: "rolling"
  measure: "Revenue"
  time_dimension: "Calendar"
  window: 3
  function: "avg"

Golden results

Cell	Value	How
Revenue YoY at [Calendar].[2025].[Q1].[Jan 2025]	0.5 (50%)	(150 − 100) / 100 = 0.5
Revenue PoP at [Calendar].[2024].[Q1].[Feb 2024]	1.0 (100%)	(200 − 100) / 100 = 1.0
Revenue YTD at [Calendar].[2024].[Q1].[Mar 2024]	600	100 + 200 + 300 = 600
Revenue R3 at [Calendar].[2025].[Q1].[Mar 2025]	250	avg(150, 250, 350) = 250

Tip

The formatString="0.0%" on Revenue YoY and Revenue PoP tells XMLA clients to render the raw ratio (0.5) as a percentage (50.0%). Omit it to receive the raw decimal.

Sample MDX query

SELECT
  { [Measures].[Revenue],
    [Measures].[Revenue YoY],
    [Measures].[Revenue PoP],
    [Measures].[Revenue YTD],
    [Measures].[Revenue R3] } ON COLUMNS,
  [Calendar].[Month].Members ON ROWS
FROM [Monthly Revenue]

Partial result (2024–2025 Jan through Mar):

Month	Revenue	YoY	PoP	YTD	R3
Jan 2024	100	—	—	100	100
Feb 2024	200	—	100.0%	300	150
Mar 2024	300	—	50.0%	600	200
Jan 2025	150	50.0%	−50.0%	150	216.7
Feb 2025	250	25.0%	66.7%	400	233.3
Mar 2025	350	16.7%	40.0%	750	250

Dashes (—) indicate NULL — no prior year data or no predecessor period is available.

Relationship to <CalculatedMembers>

<TimeCalc> declarations desugar at load time into <CalculatedMember> elements on [Measures]. The generated members are indistinguishable from hand-written calculated members at query time: they appear in XMLA member enumerations, they respond to FORMAT_STRING, and they can be referenced by other calculated members.

If you need a formula that <TimeCalc> cannot express — for example, a custom blended metric or a multi-measure ratio before a time comparison — use a plain <CalculatedMember> directly. The two approaches can coexist in the same cube.

See Advanced — Calculated members for the full <CalculatedMember> reference.