Forum Discussion

sprao's avatar
sprao
Helper I
3 years ago
Solved

POWER BI

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    
Need Help
Tips and Tricks
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
sprao
Helper I
3 years ago

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

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    3 years ago

    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.