topic Re: Complex Compounding Interest Rates in DAX Commands and Tips

Complex Compounding Interest Rates

tkfisher — Mon, 06 Jun 2022 17:25:23 GMT

Hi All,

I have a table with dates, "growth rates" and product keys. See snapshot below:

Each product category may have a different "growth rate" for each date, though you can only see Jan 2022 in this snapshot (it goes out 24 months).

Here is my equation. Unfortunately it looks like it sums up the entire calculation with my formula below.

Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'date table'[date] )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)

Ideally this would represent the compounding growth rate of each Quickname and Date. Any help would be greatly appreciated!

Re: Complex Compounding Interest Rates

tamerj1 — Mon, 06 Jun 2022 18:11:35 GMT

Hi tkfisher

"Ideally this would represent the compounding growth rate of each Quickname and Date."
My understanding is that, this is a unique column so what is the use of PRODUCTX? Why not "for each quick name" only? And where is the category you have mentioned at the begining of your post?

I might be mistaken but you may try

Compounding Value = VAR DateQuickNameTable = CALCULATETABLE ( 'date table', ALLEXCEPT ( 'date table', 'date table'[Quickname] ) ) VAR LatestDate = MAXX ( DateQuickNameTable, 'Collection Growth Table'[date] ) RETURN PRODUCTX ( FILTER ( DateQuickNameTable, 'Collection Growth Table'[Date] <= LatestDate ), 1 + 'Collection Growth Table'[Value] )

Re: Complex Compounding Interest Rates

tkfisher — Mon, 06 Jun 2022 19:43:47 GMT

Apologies, should have been clearer. I have been trying a few things and so my DAX was getting mixed together. Here is realistically where I am at now:

Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'date table' )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)

Filtering on just one Quickname (apologies, I said category before but meant Quickname) shows my issue more clearly:

What I would like is a column that compounds the 'Value' column month over month, but does it separately for each Quickname.

Re: Complex Compounding Interest Rates

tkfisher — Mon, 06 Jun 2022 19:48:26 GMT

Sorry was wrong on my DAX again.... here you go

Compounding Value =
VAR LatestDate =
MAX ( 'Collection Growth Table'[date] )
VAR UnfilteredTable =
ALL ( 'Collection Growth Table' )
RETURN
CALCULATE (
PRODUCTX ( 'Collection Growth Table', 1 + 'Collection Growth Table'[Value] ),
FILTER (
ALL ( 'Collection Growth Table' ),
'Collection Growth Table'[Date] <= LatestDate
)
)

Re: Complex Compounding Interest Rates

tamerj1 — Mon, 06 Jun 2022 20:10:02 GMT

tkfisher

Ok. Please try

Compounding Value = VAR DateQuickNameTable = CALCULATETABLE ( 'date table', ALLEXCEPT ( 'date table', 'date table'[Quickname] ) ) VAR CurrentDate = 'Collection Growth Table'[date] RETURN PRODUCTX ( FILTER ( DateQuickNameTable, 'Collection Growth Table'[Date] <= CurrentDate ), 1 + 'Collection Growth Table'[Value] )

Re: Complex Compounding Interest Rates

tkfisher — Mon, 06 Jun 2022 20:17:28 GMT

This works, thank you!