Lookupvalue trouble

[baronraghu_3011]

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]

Hi,

I tried doing as you said, the formula didn give me any error but however, the return value was blank

[Barthel · Accepted solution]

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

[Barthel]

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