How to Get Divide a Column by Its Start Value Based Its Filter Date Range
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_return where 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

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

Sophia Al-Mansoor

Sophia Al-Mansoor

Global Business & E-Commerce Reporter

Sophia analyzes international trade, startup ecosystems, retail transformation, and supply chain logistics for modern digital publications.

Share this article
Twitter Facebook Pinterest