Max Price based on Max Date

[andrew_sell]

Hi,

I am looking for some help on how to obtain the most recent price of an item that has been purchased multiple times. I want to display the price based on the Max date in a table. My raw data set looks like below. 

PO Date	Item	PO Descr	Price USD
2/22/2019	A6610407277	TAPE; SEALANT, PTFE, 1-1/2" W X 520' L	15.28
2/4/2019	A6610407277	TAPE; SEALANT, PTFE, 1-1/2" W X 520' L	15.28
12/20/2018	A6610407277	TAPE; SEALANT, PTFE, 1-1/2" W X 520' L	15.27
10/8/2018	A6610407277	TAPE; SEALANT, PTFE, 1-1/2" W X 520' L	15.27
11/10/2017	A6610000454	BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE	11.39
3/13/2017	A6610000454	BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE	11.83
2/2/2016	A6610000454	BEARING,BALL: FAFNIR MFG, 205KDD MFG P/N, CONRAD TYPE, SINGLE ROW, 0.9843" INSIDE DIAMETER, 2.0472" OUTSIDE DIAMETER, 0.5906" WIDTH, (2) SHIELD STYLE, 25BC02JPP AFBMA, 11561 IBI	11.69
10/22/2018	A6610407096	SEAL GENERAL 63INFABRISEAL 8"WX19LF	2348.18
3/13/2017	A6610407096	SEAL GENERAL 63INFABRISEAL 8"WX19LF	2268.93
12/5/2016	A6610407096	SEAL GENERAL 63INFABRISEAL 8"WX19LF	2268.93
11/15/2016	A6610407096	SEAL GENERAL 63INFABRISEAL 8"WX19LF	2268.93

And what i would like to display would be below:

PO Date	Item	PO Descr	Price USD
2/22/2019	A6610407277	TAPE; SEALANT, PTFE, 1-1/2" W X 520' L	15.28
11/10/2017	A6610000454	BEARING,BALL:FAFNIR,205KDD,CONRAD,SINGLE	11.39
10/22/2018	A6610407096	SEAL GENERAL 63INFABRISEAL 8"WX19LF	2348.18

[Vegar]

Prices:
Load
   [PO Date] ,
   Item,
   [PO Descr],
   [Price USD]
FROM 
   RawData
;
Right join (Prices)
Load
   Max([PO Date]) as [PO Date],
   Item
Resident 
   Prices
Group by 
   Item
;

[morenoju]

Hi Andrew,

I think you could use FirstSortedValue.

FirstSortedValue(Item, -PO Date) would show the first value of Item based on the max date.

I would create a table with the following columns:

PO Date
FirstSortedValue(Item, -PO Date)
PO Descr
Price

Regards,

Juan

[trdandamudi]

In a straight table create as below:

Dimension:

[PO Date]

Item

[PO Descr]

Expression:

=Aggr(Max([Price USD]),Item)

Hope this helps.

[Shubham_Deshmukh]

Pls confirm, you value for 10/22/2018 is 2348.18 ? or it should be 15.27.

[andrew_sell]

Value should be 2348.18 as that is the newest price for that Item ID