Forum Discussion

davidi4524's avatar
davidi4524
Helper 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$

 

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/

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,

4 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    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,

    • davidi4524's avatar
      davidi4524
      Helper III
      9 years ago

      thank you very much!!!!!!

       

      its work for me. great!

       

      thanks.

      • davidi4524's avatar
        davidi4524
        Helper III
        9 years ago

        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's avatar
    MarcelBeug
    Community 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.