Rolling Product Date slicer by item

[Jadson]

Hi, I'm new in power BI and I have a problem I cant solve. I have the following table in my desktop where value is the 1 + return of Id_fund in that period:

From the table above, I want to plot an accumulated return graph for each one ID_Fund that varies as I change the date range.

Just a example to make it clearer: The plot below shows the accumulated return from 22/10/2014 to 24/04/2016 for Id_fund 34. 

However, if I change the startdate to 12/10/2015, the start value is not 1, but the accumulated value since 22/10/2014 (1.15). I need the accumulated return to reset according startdate, that is, the start point in plot shoud be 1 and the return will accumulated over time. (PS: My first date in database is 22/10/2014)

For solve this, I split the problem in two parts: First, I have accumulated the return by Id_Fund. Then, I have to make this accumulated return vary according date slicer. I tried the following code but it's still not that I need. This code only solve the first part of problem:

Column =

VAR _min = CALCULATE(MIN(NAV_SHARE[Dt_FundNavDate]),ALLSELECTED(NAV_SHARE[Dt_FundNavDate]))

VAR _max = CALCULATE(MAX(NAV_SHARE[Dt_FundNavDate]),ALLSELECTED(NAV_SHARE[Dt_FundNavDate]))

RETURN

CALCULATE(

PRODUCT(NAV_SHARE[value]),

FILTER(

ALL(NAV_SHARE),

NAV_SHARE[Dt_FundNavDate] <= EARLIER(NAV_SHARE[Dt_FundNavDate])

&& NAV_SHARE[Id_Fund] = EARLIER(NAV_SHARE[Id_Fund])

&& _min >= NAV_SHARE[Dt_FundNavDate]

)

)

Thanks!

Jadson

[halfglassdarkly · Accepted solution]

Hi Jadson, try adding this as a measure instead of a calculated column and see if that works for you. A calculated column isn't going to reflect the filter context applied with your slicers but a measure will.

The measure below should return 1 if the date is the earliest date selected based on your slicers, and otherwise return the cumulative product of your value column within your slicer criteria. If more than one ID is chosen it will return an error.

Measure =

//Store min selected date value

VAR _min = CALCULATE(MIN(NAV_SHARE[Dt_FundNavDate]),ALLSELECTED(NAV_SHARE[Dt_FundNavDate]))

//Store min selected date value where date > _min

Var _NextMin = CALCULATE(Min(NAV_SHARE[Dt_FundNavDate]),ALLSELECTED(NAV_SHARE[Dt_FundNavDate]),NAV_SHARE[Dt_FundNavDate]>_min)

//Store current date value (changes based on visual context)

VAR _CurrentDateVal = MAX(NAV_SHARE[Dt_FundNavDate])

//Store the selected ID. If more than one ID is selected return error

VAR _SelectedID = if(HASONEVALUE(NAV_SHARE[Id_Fund]),Max(NAV_SHARE[ID_Fund]),ERROR("Please select one ID"))

RETURN

if(_CurrentDateVal=_min,1,

CALCULATE(

PRODUCT(NAV_SHARE[Value]),ALL(NAV_SHARE),

NAV_SHARE[Dt_FundNavDate] <= _CurrentDateVal,

NAV_SHARE[Id_Fund] = _SelectedID,

NAV_SHARE[Dt_FundNavDate] >=_NextMin))

[Jadson]

Hi @halfglassdarkly, thank you very much. Your solution solved my problem!