Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing open and close on different dates

Hi all,   I'm wondering if anyone can help with something I'm trying to find a solution for.   I'm trying to find the difference between the value of ‘open’ on the date 02/01/2014 with the value ...
  • johnt75's avatar
    johnt75
    4 years ago

    In this case I would create a calculated table as your criteria are fixed and don't need to be affected by slicers.

    Comparison Table = GENERATE( VALUES( 'Stock'[symbol]),
var symbol = 'Stock'[Symbol]
var openPrice = LOOKUPVALUE( 'Stock'[open], 'Stock'[Symbol], symbol, 'Stock'[Date], DATE(2014,1,2))
var closePrice = LOOKUPVALUE( 'Stock'[close], 'Stock'[Symbol], symbol, 'Stock'[Date], DATE(2017,11,29))
return ROW( "Open", openPrice, "Close", closePrice, "Diff", closePrice - openPrice)
)

    You can then use this new table in visuals or in calculations

johnt75's avatar
johnt75
Super User
4 years ago

As tamerj1  says, if there are duplicates in the data they would be treated differently by our different approaches.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks John.

 

Though to add to my confusion, I've just manually opened the data set and done the calculation for one of the stocks, and I've received a different value entirely than both of these solutions provide.

 

Going a bit numbers blind now, ha.