Forum Discussion

tkfisher's avatar
tkfisher
Frequent Visitor
4 years ago
Solved

Complex Compounding Interest Rates

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...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
tkfisher
Frequent Visitor
4 years ago

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's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    tkfisher
    Frequent Visitor
    4 years ago

    This works, thank you!