Indexing time series of performance with Dynamic Date Filter

[Johnsnowlife]

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? 

[v-jiascu-msft]

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

[Johnsnowlife]

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

[v-jiascu-msft · 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] )
            )
        )
    )

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

[Johnsnowlife]

Brilliant! Thank you so much!

[CahabaData]

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

[Johnsnowlife]

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?

[CahabaData]

be sure your date field is actually set as a date and not set to be a text field.