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...
  • askhanduja's avatar
    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.