help with find data thru dates

[davidi4524]

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$

the second table is oredrs , that contain for example itemnum and price.

i have an order for iphone on a date that not shown on the itemlist table , for example:

date = 7.6.17

itemnum = iphone

i whant to bring column that will show the price of the iphone on this date , based on the data in itemlist table  ' its supposed to be 15$ , because the date 7.6.17 is between 2.6.17 and 9.6.17.

can be done??

thanks/

[v-sihou-msft · Accepted solution]

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

[davidi4524]

thank you very much!!!!!!

its work for me. great!

thanks.

[davidi4524]

i have some orders with a date before the first date on the itemlist table, in this situation the lookupvalue intreduse "0".

i whant him to intreduce the first price from the first date that exist in itemlist table.

please help me.

thanks.

[MarcelBeug]

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.