cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## 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
Employee

@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 4
Employee

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

Helper III

thank you very much!!!!!!

its work for me. great!

thanks.

Helper III

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.

thanks.

Community Champion

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

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)

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors