Dynamic Growth Rate?

[#1]

I'm trying to calculate a growth rate between the two user-selected endpoints of my data, but I can't figure out how to make the formula work.  For instance, if the user selects 2013-2016, I'd like to display the growth rate for that period.  Similarly, if they select 2010-2019, I'd like to display the growth rate for that period as well.

Current variables are [FiscalYear] and [Amount]

[maxgro]

are the end point years?

maybe growth in % / number of years?

( sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount) -  sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount) )

/ 

( sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount) * (Max([Fiscal Year]) -Min([Fiscal Year]))

[#3]

The end points should be the first and last years within the selected years in [FiscalYear] - if the user selects 14-19, it should be 14 and 19.  And yes, it should be % growth over the user selected period (so % growth from 14-19 in the aforementioned example).

[maxgro]

ok, thanks, my idea is above, hope it helps you

[#5]

I entered your suggestion (had to close the parentheses at the end) but it's just giving me null values.  Any idea?

[maxgro]

if you think the idea is ok (from the logical point of view) you can start to check an expression

sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount)

as there are 3 similar expression, when the first one is ok, the other too should be ok

assuming you select the period 2013-2016, previous expression should be the same (same value) as

sum( {$<[Fiscal Year]= {2016}>} Amount)

[#7]

When I just enter one section of the formula with 2014-2017 selected:

(sum( {$<[Fiscal Year]= {$(=Max([Fiscal Year]))}>} Amount)

or

(sum( {$<[Fiscal Year]= {$(=Min([Fiscal Year]))}>} Amount)

It returns the same value - the sum total of 2014-2017.  Any ideas?

[maxgro · Accepted solution]

just tried in the attachment with some test data

[#9]

EDIT:  Figured it out - the issue was on my end.  Your solution worked beautifully.  Thank you!