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

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

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