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

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi John,

         

        Thank you very much, this has returned the intended results. I had got myself mixed up between the two.

         

        May I ask why we used DAX for this, as opposed to PowerQuery? Is it not possible in it?

         

        Would you also mind please walking me through each part of the DAX code so I can understand exactly which part is doing what? I know what's happening, but to be more specific;

        var openPrice = LOOKUPVALUE( 'Stock'[open], 'Stock'[Symbol], symbol, 'Stock'[Date], DATE(2014,1,2))

        This part of the code, what is each part doing?

         

        Thank you,

         

        Ben