cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fcoatis
Post Patron
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 :

Fig1.png Fig2.png

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

 

 

1 ACCEPTED SOLUTION
askhanduja
Helper I
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.

View solution in original post

3 REPLIES 3
Fcoatis
Post Patron
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

 

askhanduja
Helper I
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.

View solution in original post

@askhanduja Thank you!!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors