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. 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])
    )
)

 

 

Thank you in advance.

 

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

 

 

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.

3 Replies

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