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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 
Try this

Difference =
VAR OpenDate =
    DATE ( 2014, 2, 1 )
VAR CloseDate =
    DATE ( 2017, 12, 29 )
VAR OpenValue =
    CALCULATE ( SUM ( Table[open] ), Table[Date] = OpenDate )
VAR CloseValue =
    CALCULATE ( SUM ( Table[close] ), Table[Date] = CloseDate )
RETURN
    CloseValue - OpenValue
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Tamerj1,

 

This is what I'm receiving. 

I know I should place 'Table' with the name of my table, but I've left as is for the purpose of screenshotting this (as it's very long in comparison).

 

I'm trying to understand what this is doing; is this creating something (a variable?) called OpenDate, which is the date specified, same for CloseDate.

 

Then from lines 5 through to 10, then it's creating another for OpenValue and Close Value, then minusing the open from the close?

 

I have since managed to get another response (johnt75's reply) to work, just to let you know. But I still appreciate this as it's all learning!

 

Thanks,

 

Ben