Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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! 🙏
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! 🙏
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |