Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I am trying to plot the performance of a mutual fund over time. I want the values to start at 100 for whatever start date I choose. And then to grow by the geometric product of the performance. My data looks as follows:
Date | Fund | Performance |
01/01/2010 | Equity Fund | 0% |
02/01/2010 | Equity Fund | 2% |
03/01/2010 | Equity Fund | 5% |
So if my date range on my report starts at 01/01/2010, the line should go from 100 to 102 to 107.1. If it starts at 02/01/2010 it should go from 100 to 105.
How can I do this?
Solved! Go to Solution.
Hi @Johnsnowlife,
The function "allexcept" would help.
Result2 = VAR minDate = CALCULATE ( MIN ( 'EqPerf'[Date] ), ALLSELECTED ( EqPerf[Date] ) ) RETURN IF ( MIN ( 'EqPerf'[Date] ) = minDate, 100, CALCULATE ( PRODUCTX ( 'EqPerf', ( 1 + 'EqPerf'[Performance] ) ) * 100, FILTER ( ALLEXCEPT ( 'EqPerf', EqPerf[Portfolio] ), 'EqPerf'[Date] > minDate && 'EqPerf'[Date] <= MIN ( 'EqPerf'[Date] ) ) ) )
1. You could use a slicer of [Portfolio] or add it into "legend".
2. DAX is very dynamic. It isn't a good idea to hardcode the [Portfolio]. One formula (result 2) is enough.
3. It's very hard to iterate a row context here in this scenario. At least for me. Result 2 is a measure. If you want to have a row context in the report view, you need to use some functions. Such as "filter", "sumx" and functions end with "x" usually.
Best Regards!
Dale
Hi @Johnsnowlife,
As I tested, you need a measure.
Result = VAR minDate = CALCULATE ( MIN ( 'Table1'[Date] ), ALLSELECTED ( Table1[Date] ) ) RETURN IF ( MIN ( 'Table1'[Date] ) = minDate, 100, CALCULATE ( PRODUCTX ( 'Table1', ( 1 + 'Table1'[Performance] ) ) * 100, FILTER ( ALL ( 'Table1' ), 'Table1'[Date] > minDate && 'Table1'[Date] <= MIN ( 'Table1'[Date] ) ) ) )
Best Regards!
Dale
Success! You're a champ! Thanks
BUT...
the table actually has multiple funds in it which need their performance calculated separately. The fund's name is in the 'Table1'[Portfolio]' column.
I can't extract the portfolio name into a VAR at the beginning because the VAR is not evaluated per row context, hey?
Creating nested IF statements will make the code quite messy. So how can I feed the row context value in the [Portfolio] into the filter?
I've done it with the "Equity" fund, but I need it also evaluated for "ALSI" and several others.
Result2 = VAR minDate = CALCULATE ( MIN ( EqPerf[Date] ), ALLSELECTED ( EqPerf[Date] ) ) RETURN IF ( MIN ( EqPerf[Date] ) = minDate, 100, CALCULATE ( PRODUCTX ( EqPerf, ( 1 + EqPerf[Performance] ) ) * 100, Filter( All(EqPerf), EqPerf[Date] > minDate && EqPerf[Date] <= MIN ( EqPerf[Date] )
&& EqPerf[Portfolio] = "Equity" ) ) )
Hi @Johnsnowlife,
The function "allexcept" would help.
Result2 = VAR minDate = CALCULATE ( MIN ( 'EqPerf'[Date] ), ALLSELECTED ( EqPerf[Date] ) ) RETURN IF ( MIN ( 'EqPerf'[Date] ) = minDate, 100, CALCULATE ( PRODUCTX ( 'EqPerf', ( 1 + 'EqPerf'[Performance] ) ) * 100, FILTER ( ALLEXCEPT ( 'EqPerf', EqPerf[Portfolio] ), 'EqPerf'[Date] > minDate && 'EqPerf'[Date] <= MIN ( 'EqPerf'[Date] ) ) ) )
1. You could use a slicer of [Portfolio] or add it into "legend".
2. DAX is very dynamic. It isn't a good idea to hardcode the [Portfolio]. One formula (result 2) is enough.
3. It's very hard to iterate a row context here in this scenario. At least for me. Result 2 is a measure. If you want to have a row context in the report view, you need to use some functions. Such as "filter", "sumx" and functions end with "x" usually.
Best Regards!
Dale
Brilliant! Thank you so much!
You will want to create a measure and use the dax EARLIER function to multiply the current Performance by the earlier Value (I just made up that term i.e. 102 on 02/01/10)
https://msdn.microsoft.com/en-us/library/ee634551.aspx
PBI doesn't allow me to enter that as a Column or a Measure.
Error reads: A single value for column 'Performance1' in table 'EqPerf' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Or did I misunderstand what formula you're suggesting?
be sure your date field is actually set as a date and not set to be a text field.
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the October 2025 Power BI update to learn about new features.