apply a compounding interest rate

Question

Hello, &nbsp;I am attempting to project budget amounts for multiple years using a compounding rate increase which I will control using a parameter. &nbsp;My data set looks like: &nbsp;Year Budget (all the same based on base year) 2019 $XXX,XXX2020 $XXX,XXX20212022202320242025&nbsp;Here is my base formula that I cannot get to work. All thoughts are welcomed. &nbsp;Annual Budget Increase2 = [2019 Budget] &nbsp;&nbsp;&nbsp; * POWER (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1 + 'Projected Annual Budget Change'[Projected Annual Budget Change],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEDIFF (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FIRSTDATE ( 'Budget'[Year] ), LASTDATE('Budget'[Year]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 'Budget',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; YEAR&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp; ))&nbsp;Thanks much.

greg_deckler · Accepted Answer

See Page 3, Table 13
&nbsp;
Measure 7 = 
VAR __2019Budget = MAXX(FILTER(ALL('Table13'),[Year]=2019),[Budget])
VAR __currentYear = MAX([Year])
VAR __minYear = 2019
RETURN
__2019Budget * POWER(1+[Rate Increase], __currentYear - __minYear)

Forum Discussion

apply a compounding interest rate

2 Replies

Related content

Compounding Interest Table

calculate compound interest

table with compound interest in dax

Complex Compounding Interest Rates

Interesting effects with calendar-based time intelligence