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

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))
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    johnt75
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.