date return
1/1/2010 1.01
2/1/2010 1.02
3/1/2010 1.03
4/1/2010 1.04
5/1/2010 1.05
6/1/2010 1.06
7/1/2010 1.07
8/1/2010 1.08
9/1/2010 1.09 

Hi, There, I have cumulative return data (factor_return). I want a measure = return / start_returnwhere start_return is the return of the earliest date in the filtered date range.

Thanks

I tried this, but got complained "cannot find name factor_return[ return]". Please help.

Measure =
VAR start_return = CALCULATE ( VALUES ( factor_return[ return] ), FILTER ( ALLSELECTED ( factor_return[date] ), factor_return[date] = MIN ( factor_return[date] ) ) )
RETURN DIVIDE ( factor_return[ return], start_return )

2

2 Answers

Well first, your error means that you misspelled your column. I can see you added a white space before 'return', there is a difference between [ return] and [return]. Note the space in the first example.

Secondly, your formula wouldn't get you what you want because the MIN() statement would be affected by the filtercontext outside of the current filtered table context. It would search for the min date in a table of only 1 row (the current date row). The following measure will work for you:

Measure =
VAR start_return = CALCULATE(VALUES(factor_return[return]), FILTER(ALLSELECTED(factor_return), factor_return[date] = MINX(ALLSELECTED(factor_return), factor_return[date])))
RETURN
DIVIDE(SELECTEDVALUE(factor_return[return]), start_return)

Measure 2 in the following screenshot shows the value of VAR start_return.

Hope this helps!

I'd do it like this:

Measure =
VAR start_date = CALCULATE ( MIN ( factor_return[date] ), ALLSELECTED ( factor_return ) )
VAR start_return = CALCULATE ( SELECTEDVALUE ( factor_return[return] ), factor_return[date] = start_date )
RETURN DIVIDE ( SELECTEDVALUE ( factor_return[return] ), start_return )

This calculates the first date within your filter, looks up the return on that date, then divides the current return by that first return.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy