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
Community Champion
4 years ago

Hi Anonymous 
You may try 

Difference =
VAR OpenDate = "02/01/2014"
VAR CloseDate = "29/12/2017"
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,

 

Thank you for your response.

 

I am getting this error when using the code you provided;

Any suggestions?

 

Could you also please explain what it is this is trying to do? I'm new to PowerQuery/DAX so am trying to learn and understand as much as I can.

 

Thanks,

 

Ben

  • tamerj1's avatar
    tamerj1
    Community 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

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        No this is a measure. Click on "New Measure" and paste the complete code including the name. 
        After that choose the matrix visual and drag the symbol column to the raws and the new measure to the values