cancel
Showing results for
Did you mean:
Post 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. here is my sample.

I'm trying to adapt this code with no success:

``````cumul_norm_return_summarizefree =
CALCULATE(
PRODUCTX( ALLSELECTED(perf[date]), (1+[norm_return]))-1,
FILTER(
ALLSELECTED(perf), perf[date]<=MAX([date]) &&
perf[fund] IN FILTERS(perf[fund])
)
)``````

Update 9/12:

I got the expected result using this code in transactions table:

``````Acc Return =
CALCULATE(
PRODUCTX(Transactions,[Return]),
FILTER(ALL(Transactions[Data]),
Transactions[Data]<=MAX(Transactions[Data])
)
)``````

But I´m curious regarding the Date Table. How can I accomplish the result using dates from date dimension?

Best Regards

1 ACCEPTED SOLUTION
Helper 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
__CumulativeReturn``````

I've also uploaded the file here.

If this answers your query, please mark it as the solution and a thumbs up would be great.

3 REPLIES 3
Post Patron

@askhanduja solution actually gave me this insight:

``````Return Amount RP =
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR FirstVisibleDate = MINX(ALLSELECTED('Date'[Date]), ('Date'[Date]))
VAR Result =
CALCULATE (
PRODUCTX(Transactions,[Return]),
DATESBETWEEN('Date'[Date],FirstVisibleDate,CurrentDate)
)
RETURN
Result``````

Helper 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
__CumulativeReturn``````

I've also uploaded the file here.

If this answers your query, please mark it as the solution and a thumbs up would be great.

Post Patron

Announcements