How to get the last price you got in a product.

[JorgeCervantes]

Hi Everyone:

So I'm having the following issue, I have a table with multiple products that have been purchased in different prices troughout the years. I've made a calculated table to have the single values for the products and the last date a product was bought each year, now I want to know the price according to that last date the product was purchased on. Something like this:

I want to transform this: 

Date	Product	Price
9/10/2023	Product A	15
11/16/2023	Product A	12
12/12/2023	Product A	14
7/5/2023	Product B	13
8/8/2023	Product B	15

Into this:

Product	Last Date 2023	Last Price 2023
Product A	12/12/2023	14
Product B	8/8/2023	15

Does anyone know how can I solve this? I have tried with LOOKUPVALUES, FIRSTVALUE and LASTVALUE, but I still can't find the solution.

Thank you.

[Daniel29195 · Accepted solution]

@JorgeCervantes 

output

calculated table : 

new_tbl = 
ADDCOLUMNS(
    VALUES(tbl[   Product]),
"latest date" , CALCULATE(MAX(tbl[Date]))
)

calculated col : 

cooresponding price = 
MAXX(FILTER(tbl, tbl[   Product] = new_tbl[   Product] && tbl[Date] = new_tbl[latest date]),tbl[  Price])

hope this helps .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🙏