Pick the value of highest date transation file

[josecarmo]

To use on a function KPI chart or a Table , how do I pick the last  date record field "Onhand" field for a product and not the SUM of the transactions.

Take this example bellow I would like to show for produt 001 the Onhand 3200 value once is the record with highest date.

DailyStocks:

Load Stock As Date, Product, Onhand;

LOAD * inline [

Date|Product|Onhand

04/01/2021|001|20000

04/01/2021|002|30000

05/01/2021|001|35000

05/01/2021|002|30000

06/01/2021|001|32000

06/01/2021|002|26000

 ] (delimiter is '|');

[GaryGiles · Accepted solution]

Use the FirstSortedValue function.  If you have Product as a dimension in your table object,

=FirstSortedValue(Onhand,-Date)

should give you the latest Onhand value for each Product.

[lironbaram · Accepted solution]

hi ,
assuming you use a table with the product as dimension
you can use this expression 
firstsortedvalue(Onhand,-Date)

[josecarmo]

Thanks Gary it works fine!

[josecarmo]

Thanks Lironbaram it work fine!