Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
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
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%
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
110 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |