topic Re: Calculating growth (compound interest) via measures in DAX Commands and Tips

Calculating growth (compound interest) via measures

NathanFrederick — Thu, 23 Mar 2023 19:55:55 GMT

I am trying to come up with a measure that lets me calculate "compound interest" growth, which is applied every Friday.

I've found a few solutions that work if I create tables - however, I need this to be measure based, as I have a number of different things that I need filter + have the results updated accordingly. Depending on the chosen filters, I may have different start dates, end dates, and growth rates - and to do this via table creation would involve me creating dozens (possibly hundreds) of tables - which isn't really feasible.

I have a number of measures which will return the appropriate start date/end date/start value/growth rates, based on the filters.

I need to apply the growth rate every Friday, and have added a "Week ending Friday" column to my GlobalDate table.

I'm fairly certain I need to be using ProductX for this, but I've not been able to get it to work correctly.

I threw together what I would expect to see every Friday In Excel (and subsequent days until the after the next Friday, though the excel file only has the Friday values). I'm ok with either having just Friday values, or daily values that change every Friday, however,
my current Measure will return the correct value for the total, but not the rows.

The Measure:

Growth_Measure =

VAR EndWeek = [GetGrowthEndWeek]

VAR StartWeek = [GetGrowthStartWeek]

--VAR StartValue = GetGrowthStartValue

VAR StartValue = 100 --Use 100 easier for debugging

VAR Result =

StartValue *

CALCULATE

(

PRODUCTX

(

CALCULATETABLE

(

VALUES(GlobalDate[WeekEndingFri])

,All(GlobalDate[WeekEndingFri])

,GlobalDate[WeekEndingFri] > StartWeek

,GlobalDate[WeekEndingFri] <=EndWeek

1+ [GetGrowthRate]

)

,FILTER(All(GlobalDate[WeekEndingFri]), GlobalDate[WeekEndingFri] <= EndWeek)

)

RETURN

Result

And an image of what I'm trying to acheive/what I'm getting from this, and what the date table looks like / what the growth rate function is returning per-day.

Thanks in advance for help on this!

Re: Calculating growth (compound interest) via measures

Greg_Deckler — Thu, 23 Mar 2023 20:00:01 GMT

NathanFrederick Can you just use this? CUMIPMT function (DAX) - DAX | Microsoft Learn

Re: Calculating growth (compound interest) via measures

NathanFrederick — Thu, 23 Mar 2023 20:31:07 GMT

Greg_Deckler
I'm not familiar with that function, but looking at it, I "feel" like I'm going to hit the same issue that led me to ProductX - I need to refer to "prior results" to get the next one.

The growth rate often isn't constant (in my image you can see that it's 1.6% for the first 2 weeks, then it grows each week after that for this specific piece of the data), so I'm not sure how I'd feed this the appropriate data to make it work - I know all the parameters on the intial date/period, up until the growth rate changes, and then I'd need to feed the results of the first period into pv, and repeat each time the rate changes.

If the growth rates were constant, this looks like it'd be ideal though, and I'll definitely remember it if I need a constant-growth approach 🙂