Rebased Performance Chart based on Daily Returns

[Maeaex1]

Hi Community, 

I try to calculate a performance chart over time that starts at 100 (start date). I already created a measure that calculated the daily returns. Based on these returns the performance should be calculated... The date period can be selected via a date slicer visual

The Measure for the daily returns is:

Daily PF Return = 
var end_date = MAX(V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE])
var start_date = MIN(V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE])

var r_log = CALCULATE(SUM(V_OPS_PORTFOLIO_HISTORY[Security Return Contr.]),FILTER(ALL(V_OPS_PORTFOLIO_HISTORY),V_OPS_PORTFOLIO_HISTORY[STRATEGY] = MIN(V_OPS_PORTFOLIO_HISTORY[STRATEGY]) && (V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE] >= start_date && V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE] <= end_date)))

return if(isblank(r_log), BLANK(), EXP(r_log)-1)

 The corresponding chart looks like this:

The rebased performance in (t) = (1+daily return(t)) * Rebased Performance(t-1)

As an Excel Formula it would be like this:

Any help for the rebased performance measure would be highly appreciated!

Best,

Max

[Maeaex1]

Hi @v-xinruzhu-msft,

Thanks for your reply & support! The provided formula looks promising but it appears it does not calculate my measure correclty. The "Daily Return" is in my case already a Measure as the underlying data is more complex. In the Daily Return Measure I aggregated individual returns of financial securities to get to a "Daily Return" of a Portfolio.

If I use your formula:

PF Performance = 
PRODUCTX(
FILTER(
ALLSELECTED(V_OPS_PORTFOLIO_HISTORY),
V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE] <= max(V_OPS_PORTFOLIO_HISTORY[SNAPSHOT_DATE])),
 (V_OPS_PORTFOLIO_HISTORY[Total PF Return]+1)
 )*100

then the output of the table looks like this:

Do you have an idea what's wrong? Thanks!
Max

[v-xinruzhu-msft]

Hi @Maeaex1 

The dax can work well in my test code, can you provide more sample data or other sample information?

Best Regards!

Yolo Zhu

[Maeaex1]

Hi Yolo & thanks for the ongoing support! Find below some sample data. The previously mentioned measure "Daily Return" then sums up the returns per STRATEGY & SNAPSHOT_DATE.

SNAPSHOT_DATE STRATEGY ISIN Security Return Contr.
02.01.2023 Portfolio_1 Security_1 0.0038%
02.01.2023 Portfolio_1 Security_2 0.0147%
02.01.2023 Portfolio_1 Security_3 0.0000%
02.01.2023 Portfolio_1 Security_4 0.0006%
02.01.2023 Portfolio_1 Security_5 0.0782%
02.01.2023 Portfolio_1 Security_6 0.0104%
02.01.2023 Portfolio_1 Security_7 0.0275%
02.01.2023 Portfolio_1 Security_8 0.0234%
02.01.2023 Portfolio_1 Security_9 -0.0051%
02.01.2023 Portfolio_1 Security_10 0.0232%
03.01.2023 Portfolio_1 Security_1 0.0067%
03.01.2023 Portfolio_1 Security_2 0.0056%
03.01.2023 Portfolio_1 Security_3 0.0095%
03.01.2023 Portfolio_1 Security_4 0.0073%
03.01.2023 Portfolio_1 Security_5 0.0581%
03.01.2023 Portfolio_1 Security_6 -0.0121%
03.01.2023 Portfolio_1 Security_7 -0.0119%
03.01.2023 Portfolio_1 Security_8 -0.0077%
03.01.2023 Portfolio_1 Security_9 -0.0068%
03.01.2023 Portfolio_1 Security_10 -0.0117%
04.01.2023 Portfolio_1 Security_1 0.0116%
04.01.2023 Portfolio_1 Security_2 0.0139%
04.01.2023 Portfolio_1 Security_3 0.0030%
04.01.2023 Portfolio_1 Security_4 0.0044%
04.01.2023 Portfolio_1 Security_5 0.0596%
04.01.2023 Portfolio_1 Security_6 -0.0177%
04.01.2023 Portfolio_1 Security_7 0.0152%
04.01.2023 Portfolio_1 Security_8 0.0130%
04.01.2023 Portfolio_1 Security_9 -0.0028%

[v-xinruzhu-msft]

Hi @Maeaex1 

You can refer to the following solution.

Sample data 

1.I create a measure to display the daily return +1

Daily Return+1 = SUM('Table'[Daily Return])+1

2.Create a response measure

Resonse = PRODUCTX(FILTER(ALLSELECTED('Table'),[Date]<=MAX('Table'[Date])),[Daily Return+1])*100

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.