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

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.