Forum Discussion
Complex Compounding Interest Rates
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] )
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.
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
Community Champion
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] )This works, thank you!