I have a list of firms' revenue and employment (historical and forecast). I want to calculate the compound annual growth rate (CAGR) from 2014 to the latest available year. I used the following formula,

`=(C17/C12)^(1/COUNT(C13:C17))-1`

Although the formula is effective, it cannot be expanded. This means that I would have to manually code it for each firm (there are thousands of them) and for each attribute aside from revenue and employees (there are many others). Additionally, some businesses have varied end dates; for example, some businesses' projected data finishes in 2017, while others end in 2018, etc.

Could any of the upcoming suggestions work as a remedy? Exist, other people?

1. VBA
2. PivotTable
3. PowerPivot
Oct 7, 2022 in Others 986 views

## 1 answer to this question.

The following PowerPivot DAX formulas worked for me,

```first year revenue = CALCULATE(SUM(source[revenue]),source[year]=2014)

last year revenue = CALCULATE(sum(source[revenue]),filter(source,source[year]=max(source[year])))

Forecast Revenue CAGR = ([last year revenue]/[first year revenue]) ^(1/(max([year])-2014))-1
```
• 63,420 points

+1 vote

## How to Automate OutOfMemoryError Troubleshooting?

Troubleshooting OutOfMemoryError or any memory related problem ...READ MORE

Clone script is a customizable solution that ...READ MORE

## Average TIME Calculation differs between Excel and PowerBI

They are calculating different things. In Power ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

## Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE