Last Modified Date: 21 Nov 2016
AnswerThe following instructions can be reviewed in the workbook attached below.
Step 1 - Create a Parameter
- Click the drop-down arrow to the right of Dimensions on the Data pane and select Create Parameter.
- Name the parameter "N Years"
- In Data Type > select Integer
- In Current Value > input 2
- Leave the Display format and Allowable values as they are.
- Click OK.
Step 2 - Create a Calculated Field
- Select Analysis > Create Calculated Field > name it "CAGR".
- Enter in the formula below:
POWER(ZN(SUM([Sales]))/LOOKUP(ZN(SUM([Sales])),-[N Years]), ZN(1/[N Years])) - 1
- Click OK.
Step 3 - Build the view
- Drag "Order Date" to Columns and drill down to "Quarter"
- Drag "Sales" to Rows
- Drag "CAGR" to Rows
- Right-click on "CAGR" and select Dual Axis
- Right-click on "CAGR" and select Edit Table Calculation > Choose Specific Dimension > Choose Year of Order Date only
- From the Sales Card, drag Sales to Details and select a chart as desired (in this example, the Bar chart is selected)
- From the CAGR Card, drag CAGR to Details and select a chart as desired (in this example, the Line chart is selected)
- Right-click the measure in the CAGR Card and select Edit Table Calculation > Choose Specific Dimension > Choose Year of Order Date
- Drag Sales to ToolTip if desired
Step 4 (optional) - View the data in a crosstab
- Righ-click the sheet and select Duplicate as Crosstab
- Swap Rows and Columns as desired
- Move “Year of Order Date” to the right side
Additional InformationCompound annual growth rate is calculated using the following formula, where N is the number of years:
((End Value/Beginning Value)^(1/n)) – 1Value at current date / Value at beginning date
To the power of (1/number of periods between the two dates)
Note: You can also confirm that your CAGR calculation is correct by using this third party website: http://www.investopedia.com/calculator/cagr.aspx
Thank you for providing your feedback on the effectiveness of the article.
Open new Case
Training and Tutorials