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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Johnsnowlife
Helper III
Helper III

Indexing time series of performance with Dynamic Date Filter

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:

 

DateFundPerformance
01/01/2010Equity Fund0%
02/01/2010Equity Fund2%
03/01/2010Equity Fund5%

 

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? 

1 ACCEPTED 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] )
            )
        )
    )

Indexing time series of performance with Dynamic Date Filter2 .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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] )
            )
        )
    )

Indexing time series of performance with Dynamic Date Filter.jpg

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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] )
            )
        )
    )

Indexing time series of performance with Dynamic Date Filter2 .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Brilliant! Thank you so much!

CahabaData
Memorable Member
Memorable Member

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

 

 

www.CahabaData.com

PBI doesn't allow me to enter that as a Column or a Measure. 

 

Indexed Perf Column.JPGIndexed Perf Measure.JPG

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.

www.CahabaData.com

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.