Automate compound annual growth rate CAGR calculation

Question

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

Answer 1

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