Complex Compounding Interest Rates

[tkfisher]

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!

[tamerj1]

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]
    )

[tkfisher]

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.

[tkfisher]

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
)
)

[tamerj1 · Accepted solution]

@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]
    )

[tkfisher]

This works, thank you!