cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

`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

4 REPLIES 4
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.

`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

Community Support

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

Frequent Visitor

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%

Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.