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, such as month, quarter, year, or week.

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

• You want absolute vs relative changes, for example difference vs percentage change.

• You want compound growth over multiple periods using CAGR.

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 the current period with a reference period.

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

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

Most compare-period functions keep the same output shape as the input node.

Common patterns

Compare to previous year
YOY_REL('Sales')
Use when you want to compute relative 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 it with operators such as 'Sales' - PM('Sales').

Compound growth over N periods
CAGR('Sales', 3)
Use when you want a compounded annual growth rate across a defined number of periods.

Functions in this category

Shift helpers

Generic delta functions

Standard period comparisons

Compound growth

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, or WOW for common standard comparisons.

• Use DELTA_ABS or 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, for example EUR.

• Use REL when you need a relative change, for example a growth rate.

SHIFT vs PM/PQ/PY/PW

• Use PM, PQ, PY, or PW when previous month, quarter, year, or 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 are operating on, for example month vs quarter vs year.

• CAGR errors or gaps: check whether the start and end values exist for the slice, whether NumberOfPeriods is a whole number greater than or equal to 1, and whether you need a different invalid-handling behavior.

• Behavior options: if a function supports options such as 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