User specified growth applied to multiple locations over multiple years

[Richard_H]

I have some of what I need from the answer to this post:

https://community.powerbi.com/t5/Desktop/apply-a-compounding-interest-rate/td-p/626465

I have a similar need but an extra layer to my data. The solution from the above post worked perfect for a table like this:

Year   Budget

2019 100,000

2020 100,000

2021 100,000

My data looks like this:

Location   Year   Budget

Site 1        2020   100,000

Site 1        2021   100,000

Site 2        2020   150,000

Site 2        2021   150,000

Site 3        2020    75,000

Site 3        2021    75,000

The problem I'm running into is the measure is only calculating the first site listed for each year. I feel like I'm missing a iterative function in the Dax, but I'm not sure.

Measure 7 = 
VAR __2020Budget = MAXX(FILTER(ALL('Table13'),[Year]=2019),[Budget])
VAR __currentYear = MAX([Year])
VAR __minYear = 2020
RETURN
__2019Budget * POWER(1+[Rate Increase], __currentYear - __minYear)

Thank you for looking at this post!

[v-frfei-msft]

Hi @Richard_H ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Richard_H · Accepted solution]

Hey Frank,

   So I ended up figuring out that I needed to make the following change to solve this.

Measure 7 = 
//Original Variable
// VAR __2020Budget = MAXX(FILTER(ALL('Table13'),[Year]=2019),[Budget])
// this was filtering to the first row of each year, not what I needed
VAR __currentYear = MAX([Year])
VAR __minYear = 2020
// New Variable, by using sumx I get the totals for each site I've selected with a slicer allowing me to look
//at smaller groups or all of them
VAR __SiteAnnualCost = SUMX('Table13'[Year])
RETURN
//Original output
//__2020Budget * POWER(1+[Rate Increase], __currentYear - __minYear)

// New working output
__SiteAnnualCost * POWER(1+[Rate Percent], __currentYear - __minYear)

I hope that makes sense, I know my DAX is not elegant

I had thought I needed and additional variable and a more complex formula but turns out I needed to replace  my variable and I was good to go. If you'd still like a sample let me know and I'll post it. Thanks!