cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
davidi4524 Member
Member

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$

 

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/

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: help with find data thru dates

@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])

44.PNG

 

 

Regards,

4 REPLIES 4
Super User
Super User

Re: help with find data thru dates

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.

Specializing in Power Query Formula Language (M)
Moderator v-sihou-msft
Moderator

Re: help with find data thru dates

@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])

44.PNG

 

 

Regards,

davidi4524 Member
Member

Re: help with find data thru dates

thank you very much!!!!!!

 

its work for me. great!

 

thanks.

Highlighted
davidi4524 Member
Member

Re: help with find data thru dates

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.