Compare periods

Overview

Use this category when you need to compare values across time, such as month-over-month or year-over-year changes. These functions help you shift values to a reference period and compute absolute or relative deltas.

Start here if…

• You want to compare a metric to the previous period (month/quarter/year/week).

• You want standard growth metrics such as MOM/ QOQ/ YOY.

• You want absolute vs relative changes (difference vs percentage change).

• You want compound growth over multiple periods usingCAGR.

Not here if…

• You want cumulative totals or projections → see Rollforward & time series

• You need to reshape dimensionality first → see Dimensionality & hierarchies

Mental model

• Shift: move values along a time level to align “current” with a reference period.

• Compare: compute change vs a reference period as absolute or relative difference.

• Compound: compute a compounded growth rate from a start value to an end value across N periods.

• Most compare-period functions keep the same output shape as the input node (they operate along the time axis).

Common patterns

• Compare to previous month → MOM_REL('Sales')
  Use to compute relative month-over-month change.

• Compare to previous year → YOY_ABS('Sales')
  Use to compute absolute year-over-year change.

• Compute a generic delta → DELTA_ABS('Sales', "Quarter")
  Use when you want the absolute change along a specific level.

• Shift values → SHIFT('Sales', "Month", -1)
  Use when you need explicit alignment before doing your own arithmetic.

• Use period shortcuts → PM('Sales'), PQ('Sales'), PY('Sales'), PW('Sales')
  Use when you want a direct “previous period” series (then combine with operators, for example 'Sales' - PM('Sales')).

• Compound growth over N periods → CAGR('Sales', 3).

Functions in this category

Choosing between similar functions

• CAGR vs YOY_REL (or other REL deltas)

  • Use YOY_REL when you want a single-period relative change vs last year.

  • Use CAGR when you want compounded growth across multiple periods.

• DELTA_* vs MOM/QOQ/YOY/WOW

  • Use MOM/QOQ/YOY/WOW for common standard comparisons.

  • Use DELTA_ABS / DELTA_REL when you want a delta without filling missing values.

• ABS vs REL

  • Use ABS when you need a difference in the same unit as the measure (e.g., EUR).

  • Use REL when you need a relative change (e.g., growth rate).

• SHIFT vs PM/PQ/PY/PW

  • Use PM/PQ/PY/PW when “previous month/quarter/year/week” is exactly what you want.

  • Use SHIFT when you need a custom offset or a specific time level.

Pitfalls & troubleshooting

• Unexpectedly empty output: check whether the reference period exists for those slices and whether missing values propagate through your calculation.

• Misalignment: confirm the time level you’re operating on (month vs quarter vs year).

• CAGR errors / gaps: CAGR can fail if the start/end value doesn’t exist for a slice; check whether you need a different invalid-handling behavior (e.g., ignore invalid) and ensure NumberOfPeriods is a whole number ≥ 1.

• Behavior options: if a function supports behavior options (for example FirstValueBehavior or MissingValueBehavior ), verify which option is active and whether it matches your expectation.

Related sections

• Rollforward & time series running totals, moving windows, projections

• Operators combine current vs reference values using arithmetic and comparisons

• Formula basics evaluation order, constants, and notation

• Troubleshooting guide missing values, wrong shape, unexpected results