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.

3 Replies

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