Forum Discussion
Fcoatis
4 years agoPost Patron
Accumulated Return
Just figure it out how to calculate if my model was a flat table. But i have a multi dimensional model like : I need a measure to calculate the accumulated return (ProductX) for each fund...
- 4 years ago
Hi,
The following measure would workCumulative Return = VAR __AvailableTransactionDates = VALUES('Transactions'[Date]) VAR __FirstAvailableTransactionDate = MINX(__AvailableTransactionDates, 'Transactions'[Date]) VAR __LastAvailableTransactionDate = MAXX(__AvailableTransactionDates, 'Transactions'[Date]) VAR __IsSingleDateFiltered = HASONEVALUE('Date'[Date]) VAR __SelectedDates = ALLSELECTED('Date'[Date]) VAR __MinSelectedDate = MINX(__SelectedDates, 'Date'[Date]) VAR __CurrentDate = MAX('Date'[Date]) VAR __IsMaxDateInTranRange = __CurrentDate <= __LastAvailableTransactionDate VAR __IsMinDateInTranRange = __MinSelectedDate >= __FirstAvailableTransactionDate VAR __IsSingleAssetSelected = HASONEVALUE(Asset[AssetName]) VAR __CumReturnPeriod = FILTER( __SelectedDates, 'Date'[Date] >= __MinSelectedDate && 'Date'[Date] <= __CurrentDate ) VAR __DailyReturnsUptoCurrentPeriod = CALCULATETABLE( SUMMARIZE( Transactions, Transactions[Date], Transactions[Return] ), REMOVEFILTERS('Date'), __CumReturnPeriod ) VAR __CumulativeReturn = IF( ( __IsSingleAssetSelected && __IsSingleDateFiltered && __IsMaxDateInTranRange ) || ( __IsSingleAssetSelected && NOT(__IsSingleDateFiltered) && __IsMaxDateInTranRange && __IsMinDateInTranRange ), PRODUCTX( __DailyReturnsUptoCurrentPeriod, 'Transactions'[Return] ) - 1 ) RETURN __CumulativeReturnI've also uploaded the file here.
If this answers your query, please mark it as the solution and a thumbs up would be great.
askhanduja
4 years agoHelper I
Hi,
The following measure would work
Cumulative Return =
VAR __AvailableTransactionDates = VALUES('Transactions'[Date])
VAR __FirstAvailableTransactionDate =
MINX(__AvailableTransactionDates, 'Transactions'[Date])
VAR __LastAvailableTransactionDate =
MAXX(__AvailableTransactionDates, 'Transactions'[Date])
VAR __IsSingleDateFiltered = HASONEVALUE('Date'[Date])
VAR __SelectedDates = ALLSELECTED('Date'[Date])
VAR __MinSelectedDate = MINX(__SelectedDates, 'Date'[Date])
VAR __CurrentDate = MAX('Date'[Date])
VAR __IsMaxDateInTranRange =
__CurrentDate <= __LastAvailableTransactionDate
VAR __IsMinDateInTranRange =
__MinSelectedDate >= __FirstAvailableTransactionDate
VAR __IsSingleAssetSelected = HASONEVALUE(Asset[AssetName])
VAR __CumReturnPeriod =
FILTER(
__SelectedDates,
'Date'[Date] >= __MinSelectedDate
&& 'Date'[Date] <= __CurrentDate
)
VAR __DailyReturnsUptoCurrentPeriod =
CALCULATETABLE(
SUMMARIZE(
Transactions,
Transactions[Date],
Transactions[Return]
),
REMOVEFILTERS('Date'),
__CumReturnPeriod
)
VAR __CumulativeReturn =
IF(
(
__IsSingleAssetSelected
&& __IsSingleDateFiltered
&& __IsMaxDateInTranRange
)
||
(
__IsSingleAssetSelected
&& NOT(__IsSingleDateFiltered)
&& __IsMaxDateInTranRange
&& __IsMinDateInTranRange
),
PRODUCTX(
__DailyReturnsUptoCurrentPeriod,
'Transactions'[Return]
) - 1
)
RETURN
__CumulativeReturnI've also uploaded the file here.
If this answers your query, please mark it as the solution and a thumbs up would be great.
Fcoatis
4 years agoPost Patron
askhanduja Thank you!!