CAGR

Overview

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.

Syntax

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

Example usage: CAGR('Revenue', 3)

Parameters

Parameter	Description	Type	Required	Default
Node	Input node whose values provide the end and start of the CAGR calculation	Node reference	Yes	--
NumberOf Periods	How many steps back to compare. Must be a whole number >= 1. Can also be a node without dimensionality.	Number or node reference	Yes	--
Level	The level along which periods are counted	Level name	No	Time dimension
InvalidHandling	Controls how rows with calculation errors (e.g. division by zero) are treated	Keyword	No	`"FailOnInvalid"`

InvalidHandling options:

"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

Aspect	Behavior
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) = CAGR('Revenue', 2, "Year") = 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

Input node: Revenue

Year	Product	Value
2025	Product A	100
2025	Product B	250
2025	Product C	200
2026	Product A	150
2026	Product B	250
2026	Product C	250
2027	Product A	200
2027	Product C	300

Formula: CAGR('Revenue', 1, "Product")

Year	Product	→ CAGR Result
2025	Product B	150%
2025	Product C	-20%
2026	Product B	66.67%
2026	Product C	0.00%

Product A has no prior level in the dimension hierarchy, so no CAGR can be calculated. Product B and Product C both have prior levels.

Related Functions

Function	When to use instead
YOY_REL	When you only need growth compared to one period back (no compounding). CAGR smooths growth over N periods; YOY_REL gives the raw single-period change.
YOY_ABS	When you need the absolute difference to the previous year, not a percentage growth rate.
DELTA_REL	When you need relative change along any dimension (not just time), without compounding.