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>] -
  (ParallelPeriod([<dim>].[<YearLevel>], 1, [<dim>].CurrentMember),
   [Measures].[<measure>]))
/
(ParallelPeriod([<dim>].[<YearLevel>], 1, [<dim>].CurrentMember),
 [Measures].[<measure>])

The ParallelPeriod call navigates to the same relative position one year back using the TimeYears level. The result is NULL for the first full year of data (no prior year available).

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>] -
  (PrevMember([<dim>].CurrentMember), [Measures].[<measure>]))
/
(PrevMember([<dim>].CurrentMember), [Measures].[<measure>])

PrevMember steps back one position in the dimension’s natural ordering. Results are NULL for the very first member of a level (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([<dim>].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, [<dim>].CurrentMember), [Measures].[<measure>])

Formula shape (sum, window=N):

Sum(LastPeriods(N, [<dim>].CurrentMember), [Measures].[<measure>])

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 does not exist in the cube	TimeCalc "X": measure "Y" not found in cube
No timeDimension specified and the cube has zero TIME dimensions	TimeCalc "X": cube has no TIME dimension
No timeDimension specified and the cube has more than one TIME dimension	TimeCalc "X": cube has multiple TIME dimensions — specify timeDimension
timeDimension names a dimension that does not exist in the cube	TimeCalc "X": timeDimension "Y" not found
type="rolling" and window is missing or not a positive integer	TimeCalc "X": rolling type requires a positive integer window
type="yoy" but no TimeYears level exists in the dimension	TimeCalc "X": TIME dimension has no TimeYears level

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.