Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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/
Solved! Go to Solution.
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,
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,
thank you very much!!!!!!
its work for me. great!
thanks.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |