Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |