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.
Hello All,
Please help me with returning the latest price of an item at a specific store from one table to another's column (table from SQL source, not a matrix visual)
In Table1 from SQL I have inventory information like:
Date / StoreID / itemID / Qty
22.11.2022 / store1 / item1 / 13 pieces
22.11.2022 / store2 / item1 / 10 pieces
22.11.2022 / store1 / item2 / 5 pieces
In Table2 from SQL I have sale prices like:
Date / StoreID / ItemID / UnitPrice
11.11.2022 / store1 / item1 / €3/unit
10.11.2022 / store1 / item1 / €5/unit
01.11.2022 / store2 / item1 / €4,5/unit
01.11.2022 / store1 / item2 / €4/unit
...
What I want to achieve is to return the last sales price of the items in Table1 into a new column.
The result should look like this in Table1:
Date / StoreID / itemID / Qty / UnitPrice
22.11.2022 / store1 / item1 / 13 pieces / €3
22.11.2022 / store2 / item1 / 10 pieces / €4,5
22.11.2022 / store1 / item2 / 5 pieces / €4
It's important that each item in a specific store will have a uniqe price for the last date.
Thank you!
Solved! Go to Solution.
you can try this to create a column
Column =
var _date=maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[item]&&'Table1'[date]>='Table2'[date]),'Table2'[date])
return maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[Item]&&'Table2'[date]=_date),Table2[unitprice])
pls see the attachment below
Proud to be a Super User!
you can try this to create a column
Column =
var _date=maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[item]&&'Table1'[date]>='Table2'[date]),'Table2'[date])
return maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[Item]&&'Table2'[date]=_date),Table2[unitprice])
pls see the attachment below
Proud to be a Super User!
Hi,
Thank you!
Column =
var _date=maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[item]&&'Table1'[date]>='Table2'[date]),'Table2'[date])
return maxx(FILTER(Table2,Table1[storeID]=Table2[store]&&Table1[ItemID]=Table2[Item]&&'Table2'[date]=_date),Table2[unitprice])
you are welcome
Proud to be a Super User!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |