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 |
|
Limitations | If any of the following points occurs, an error will appear:
|
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.