Forum Discussion
Lookupvalue trouble
Hi All,
I need to pickup the last traded price of a particular stock into a new table
Table 1 has last 5 years historic data of stock - ABC
Table 2 has list of stock names
i was trying Lookupvalue measure but somehow the measure was not picking a column in the search value
This is what i used - LOOKUPVALUE(Table1[Stock Price], Table1[Stock_Name],Table2[Stock Name],Table1[Date],Today()-1)
However This doesnt seem to give the result. Many Tanks for your support
baronraghu_3011
Could it be that there are multiple transactions in one day for a stock name? If there is more than one transaction, it cannot be converted to a scalar value. If this is the case, you can take the maximum stock price per day, for example.last traded price of stock = CALCULATE ( MAX ( Table1[Stock Price] ), Table1[Date] = TODAY () - 1, ALLEXCEPT ( Table2, Table2[Stock Name] ) )
3 Replies
baronraghu_3011,
If the tables are connected by a relationship (Table1[Stock Name] and Table2[Stock Name]), you could use CALCULATE instead of the LOOKUPVALUE. CALCULATE is often a better alternative. You could set up a calculated column using CALCULATE like this:last traded price of stock = CALCULATE ( SELECTEDVALUE ( Table1[Stock Price] ), Table1[Date] = TODAY () - 1, ALLEXCEPT ( Table2, Table2[Stock Name] ) )Use ALLEXCEPT to avoid a possible circular reference.
Hi,
I tried doing as you said, the formula didn give me any error but however, the return value was blank
baronraghu_3011
Could it be that there are multiple transactions in one day for a stock name? If there is more than one transaction, it cannot be converted to a scalar value. If this is the case, you can take the maximum stock price per day, for example.last traded price of stock = CALCULATE ( MAX ( Table1[Stock Price] ), Table1[Date] = TODAY () - 1, ALLEXCEPT ( Table2, Table2[Stock Name] ) )
