cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jadson
Regular Visitor

Rolling Product Date slicer by item

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:

Jadson_0-1632006581844.png

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. 

Jadson_1-1632007165753.png

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)

Jadson_2-1632007609578.png

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

1 ACCEPTED SOLUTION
halfglassdarkly
Resolver I
Resolver I

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

View solution in original post

2 REPLIES 2
halfglassdarkly
Resolver I
Resolver I

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

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.