Forum Discussion
Comparing open and close on different dates
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
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.
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?
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))Thanks, will give this a try now.
On a seperate note, another user has given the following, which provides a different result from the one you've provided;
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 - OpenValueDo you have any idea as to why they would be providing different results?