Forum Discussion
New Table Distinct value with latest value
I'm trying to create a new table based on another table, In the new table column 1 should be a distinct value(Item nr),column 3 contains lastest price filtered on the date and item nr. I've tried several solutions found on this forum but not geting the result I need.
Base Table:
| Item | Date | Price |
| 100.1 | 5/1/2022 | 10 |
| 200.1 | 5/1/2022 | 15 |
| 300.1 | 5/1/2022 | 30 |
| 100.1 | 6/1/2022 | 15 |
| 200.1 | 6/1/2022 | 20 |
| 300.1 | 6/1/2022 | 10 |
| 100.1 | 4/1/2022 | 30 |
| 200.1 | 7/1/2022 | 20 |
| 300.1 | 7/1/2022 | 15 |
Result Table:
| Item | Date | Price |
| 100.1 | 6/1/2022 | 15 |
| 200.1 | 7/1/2022 | 20 |
| 300.1 | 7/1/2022 | 15 |
Hi Anonymous ,
jgeddes 's formula works on my side.
The new table can then create a relationship with the primary table.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
With your example data you can create a calculated table with
Result Table =SUMMARIZE('Base Table','Base Table'[Item],"_latestDate", MAX('Base Table'[Date]),"_lastPrice", CALCULATE(VALUES('Base Table'[Price]),FILTER('Base Table',MAX('Base Table'[Date]) = 'Base Table'[Date])))to get the tableHi Anonymous ,
jgeddes 's formula works on my side.
The new table can then create a relationship with the primary table.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
How about this one:
I chose these columns with Date as Latest:
For the measure, I used the following DAX:
Measure = VAR _maxDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( MAX ( 'Table'[Price] ), 'Table'[Date] = _maxDate )Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
