CAGR

CAGR('Node', NumberOfPeriods [, "Level" [, "InvalidHandling"]])

The CAGR function computes the Compound Annual Growth Rate between a current value and the value N periods earlier, taking compounding into account. It works along a chosen level (e.g. Year, Month) or any non-time dimension.

Use this function when you need to measure compound growth over multiple periods, for example multi-year revenue growth: CAGR('Revenue', 3).

Parameters

NodeNode referenceRequired: Input node whose values provide the end and start of the CAGR calculation
NumberOfPeriodsNumber or node referenceRequired: How many steps back to compare. Must be a whole number >= 1. Can also be a node without dimensionality.
LevelLevel nameOptional: The level along which periods are counted. Default: Time dimension.
InvalidHandlingKeywordOptional: Controls how rows with calculation errors (e.g. division by zero) are treated. Default: "FailOnInvalid".

"FailOnInvalid": Any invalid entry causes an error. This is the default.

"IgnoreInvalid": Invalid entries are silently omitted from the result. If all rows are invalid, the result is empty.

Output shape

Dimensionality: Same as input.
Time range: The first N periods are dropped (no start value N periods back to compare against). Periods with missing start or end values are also dropped.
Values: CAGR as a ratio: (end_value / start_value)^(1/N) - 1. A result of 0.5 means 50% compound annual growth.
Row count: Reduced. At minimum, the first N periods are removed.

Watch out

If Level is omitted, the node must include a level from the time dimension.
The Level must be the lowest level in its dimension hierarchy on the input node. You cannot use a higher level (e.g. "Year" when the node has "Month" data).
The Level cannot be a linked level of another level on the node.
NumberOfPeriods must be a whole number >= 1. If it is a node, that node must have no levels.
A start value of 0 causes a division-by-zero error. Use "IgnoreInvalid" to skip these entries instead of failing.

Examples

CAGR along the time dimension

This example calculates the compound growth rate over 2 years for each country. Note that missing data points (e.g. US 2028) cause gaps in the result.

Input node: 'Revenue'

Year	Country	Value
2025	DE	100
2026	DE	200
2027	DE	300
2028	DE	400
2029	DE	500
2030	DE	600
2025	US	100
2026	US	200
2027	US	250
2029	US	300
2030	US	400

Formula: CAGR('Revenue', 2)

Equivalent to: CAGR('Revenue', 2, "Year")

Equivalent to: CAGR('Revenue', 2, "Year", "FailOnInvalid")

Year	Country	CAGR Result
2027	DE	73.21%
2028	DE	41.42%
2029	DE	29.10%
2030	DE	22.47%
2027	US	58.11%
2029	US	9.54%

There is no 2028 value for the US in the original data. This means the CAGR value cannot be calculated for 2028 (no end value) and 2030 (no start value 2 years prior).

CAGR along a non-time dimension

This example computes CAGR along the Product dimension instead of time, comparing each product to its predecessor in the dimension hierarchy.

Dimension: Product

Level Name	Level Key
Product A	1
Product B	2
Product C	3