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

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

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    I did it DAX as my first instinct because I'm much more comfortable writing that than M code, but I'm not sure that it would be possible in M because we need to use several different columns to identify the correct rows to retrieve the open and close prices from.

    The LOOKUPVALUE is getting the 'Stock'[open] value from the row where the stock symbol matches the stock symbol from the current row which is being iterated over, and also where the date matches the given date. There would be only 1 row where both those conditions are true.

    Hope this clears it up. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That does, thanks.

       

      I have noticed one issue. Stocks that did not exist on the first date are being treated as having a value of 0, which is therefore giving it the highest difference.

       

      I can manually weed out the ones with zero value in 'open', but would there be a way to do this in the coding? 

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        You could filter out the ones which didn't exist on the start date by replacing the VALUES('Stock'[symbol]) with

        CALCULATETABLE( VALUES('Stock'[symbol]), 'Stock'[Date] = DATE(2014,1,2))