Return the last price of an item at specific store, by date

[holodan95]

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!

[ryan_mayu · Accepted solution]

@holodan95 

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

[holodan95]

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])

[ryan_mayu]

you are welcome