Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Maeaex1
Frequent Visitor

Rebased Performance Chart based on Daily Returns

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:

Maeaex1_0-1702285940060.png

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

As an Excel Formula it would be like this:

Maeaex1_2-1702286267710.png

 

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

 

Best,

Max

 



4 REPLIES 4
Maeaex1
Frequent Visitor

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:
Maeaex1_0-1702477652273.png


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

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

 

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
Community Support
Community Support

Hi @Maeaex1 

You can refer to the following solution.

Sample data 

vxinruzhumsft_0-1702452937765.png

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

vxinruzhumsft_1-1702453048247.png

 

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.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.