Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
4 years ago
Solved

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...
Need Help
  • askhanduja's avatar
    askhanduja
    4 years ago

    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.

Fcoatis's avatar
Fcoatis
Post Patron
4 years ago

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