CAGR

Basic Overview

Description	The Compound Annual Growth Rate (CAGR) function measures an investment's annual growth rate over time, with the effect of compounding taken into account.
Signature	CAGR(Node, NumberOfPeriods [, Level])
Parameters	Node1: An input node with values to use for start and end dates in the calculation NumberOfPeriods: The number of dimension level values previous to each level value taken into comparison, e.g. if the current level value is 2022 and the NumberOfPeriods is 2, then the start level value will be 2020 NumberOfPeriods can also be a node. Level: The name of the level to find the first values in respect to (see limitations for values allowed). If no [Level] is provided, the time dimension will be used.
Limitations	If any of the following points occurs, an error will appear: If no value is entered for [Level], and the entered node does not include a level from the time dimension The value entered for [Level] is not used by the entered node The value entered for [Level] is higher in the dimension than the level used in the node e.g. if the node uses ‘Month’ the inputted level is ‘Year’ The entered value for [Level] is a linked level of another level used by the entered node The NumberOfPeriods value is not a whole number equal to 1 or greater If NumberOfPeriods is a node, it cannot have any levels available.

Example

Input:

Node A

Year	Country	Revenue
2020	DE	100
2021	DE	200
2022	DE	300
2023	DE	400
2024	DE	500
2025	DE	600
2020	US	100
2021	US	200
2022	US	250
2024	US	300
2025	US	400

To find the CAGR value based on the two years before for each country, the following configuration of the function would be used:

CAGR('Node A', 2) = CAGR('Node A', 2, “Year”)

Year	Country	CARG Value
2022	DE	73.21%
2023	DE	41.42%
2024	DE	29.10%
2025	DE	22.47%
2022	US	58.11%
2024	US	9.54%

Please note there is no 2023 value for the US in the original data. This means the CARG values can not be calculated for the year 2023 (as there is no end value) and 2025 (as there is no start value).

The logic works using the location of levels relative to each other in the dimension. NOT using the rows in the provided node data table. For example in the following dimension these levels exist:

Dimension: Product

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

Which is used in the following Node:

Node B

Year	Product	Revenue
2020	Product A	100
2020	Product B	250
2020	Product C	200
2021	Product A	150
2021	Product B	250
2021	Product C	250
2022	Product A	200
2022	Product C	300

Then using the function CAGR('Node B', 1, “Product”) will produce the results:

Year	Product	CAGR Value
2020	Product B	150%
2020	Product C	-20%
2021	Product B	66.67%
2021	Product C	0.00%

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