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

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's avatar
tamerj1
Community Champion
4 years ago

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

    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's avatar
      tamerj1
      Community Champion
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.