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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,
You can try making this calculated column

Difference =
CALCULATE('Table'[close],'Table'[date] = DATE(2017,12,29))-
CALCULATE('Table'[open],'Table'[date] = DATE(2014,01,02))

  

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Aditya,

 

This is what I'm receiving from this;

 

 

Any advice?

 

Thanks.