POWER BI

[sprao]

Hi,

I want to lookup Price column into another table with latest date value only when matches the itemno

please help me how to write lookup formula

[amitchandak]

@sprao , what happen when that date is not there, assum you can take the next date

New column in item Table

Price new =

var _max = Minx(filter(Price,Price[Itemno] = item[ItemNo] && Price[Date]>= item[Date]) , price[Date])

return

Minx(filter(Price,Price[Itemno] = item[ItemNo] && Price[Date]=_max) , price[Price])

[sprao]

sorry i price table dont have Date column, i just kept understanding purpose

[v-jingzhang · Accepted solution]

Hi @sprao 

From your data, one item may have multiple prices on a date. If I understand it correctly, your original Items table is in an order that the price on the top is the earliest value and the price on the bottom is the latest value on the same date, right? With this logic, you can use Power Query Editor to add an Index column first,

Then go to Power BI Desktop, create a calculated table with below DAX:

Price Table = 
SUMMARIZE (
    ItemsTable,
    ItemsTable[Item No],
    "Price",
        CALCULATE (
            MAX ( ItemsTable[Price] ),
            ItemsTable[Index] = MAX ( ItemsTable[Index] )
        )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.