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 of ‘close’ on the date 29/12/2017, to see which specific stock increased the most over this time period.

 

The stock name is in 'symbol'

 

 

I hope this makes sense?

 

Any tips or points would be greatly appreciated.

 

  • 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

20 Replies

  • johnt75's avatar
    johnt75
    Super User
    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 Johnt75,

       

      Thanks for your reply.

       

      I am receiving the following error when trying to use this;

       

       

      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

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        Hi Ben

        You need to put my code in DAX, not in Power Query. From the Modelling tab click "New Table" and paste the code in there.

        The GENERATE function iterates over the first table, so in this case all the values for symbol, and then evaluates the second table with the context from the row in the first table, so its essentially looping through each stock symbol and for each one its returning a single row with open and closing prices and the difference between them.

  • 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 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.