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

5 Replies

  • tamerj1's avatar
    tamerj1
    Community 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
        )
        )