Comparing open and close on different dates

[Anonymous]

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.

[Aditya_Meshram]

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]

Hi Aditya,

This is what I'm receiving from this;

Any advice?

Thanks.

[tamerj1]

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]

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]

@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]

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]

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

[Anonymous]

Thanks, that is now working.

Though, another user provided a solution, which is providing different results;

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)
)

Do you have any idea as to why this would be the case?

[tamerj1]

It might be becuase you have multiple records in the same date. In this LOOKUPVALUE returns blank snd SUM aggregates the total value

[Anonymous]

Thanks Tamerj.

Though to add to my confusion, I've just manually opened the data set and done the calculation for one of the stocks, and I've received a different value entirely than both of these solutions provide.

Going a bit numbers blind now, ha.

[johnt75 · Accepted solution]

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]

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]

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]

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]

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]

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]

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))

[Anonymous]

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 - OpenValue

Do you have any idea as to why they would be providing different results?

[johnt75]

As @tamerj1  says, if there are duplicates in the data they would be treated differently by our different approaches.

[Anonymous]

Thanks John.

Though to add to my confusion, I've just manually opened the data set and done the calculation for one of the stocks, and I've received a different value entirely than both of these solutions provide.

Going a bit numbers blind now, ha.