Forum Discussion

davidi4524's avatar
davidi4524
Icon for Helper III rankHelper III
9 years ago
Solved

help with find data thru dates

hi, i have two tables the first is "itemlist" that contain two columns: itemnum, date of price , price for example:   iphone - 2.6.17 - 15$ iphone - 9.6.17 - 15.5$ iphone - 10.6.17 - 19$   t...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    davidi4524

     

    You can also achieve it via DAX approach.

     

    Make sure there's no replationships between both tables. You can create a calculated column in Order table to get the Latest Release Date from item list table.

     

    Latest Release Date = MAXX(FILTER(ALL('itemlist'),'itemlist'[Date]<=EARLIER('Order'[Date]) && itemlist[Item]=EARLIER('Order'[Item])),itemlist[Date])

    Then use LOOKUPVALUE() to get corresponding Price.

     

     

    Latest Price = LOOKUPVALUE(itemlist[Price],itemlist[Date],'Order'[Latest Release Date],itemlist[Item],'Order'[Item])

     

     

    Regards,

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

In Power Query:

 

let
    Source = Orders,
    #"Added Custom" = Table.AddColumn(Source, "Price", (EachOrder) => Table.Last(Table.SelectRows(itemlist, each [itemnum] = EachOrder[itemnum] and [date of price] <= EachOrder[date]),[price = null])[price])
in
    #"Added Custom"

 

In case of nothing found (e.g. first price date is after order date), this will return null.