Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 ;
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
In a straight table create as below:
Dimension:
[PO Date]
Item
[PO Descr]
Expression:
=Aggr(Max([Price USD]),Item)
Hope this helps.
Value should be 2348.18 as that is the newest price for that Item ID