Forum Discussion

Maeaex1's avatar
Maeaex1
Frequent Visitor
2 years ago

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

 



Need Help
Tips and Tricks

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

     

  • Maeaex1's avatar
    Maeaex1
    Frequent Visitor
    2 years ago

    Hi Anonymous,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Maeaex1
        Frequent Visitor
        2 years ago

        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%