Return the Max date of multiple IDs

[jhew1234]

I have a shortage table which contains the delivery dates of shortage components and a separate Orderbook table which contains customers purchase orders (made up of multiple components) these are connected through a primary key the sales order.   

I am trying to add a calculated column to the orderbook table, or can do it in a load statement (whichever is more logical).  Where I return the latest date of the components linked to that sales order. 

My idea was to use MAX to find the latest date and then match the sales orders but no luck!  

= MAX({match([Sales Order],[Sales Order])}, [Delivery/order finish date])

How would I go about solving this? 

Thanks. 

[Ksrinivasan · Accepted solution]

hi,

use this example, my dummy data

table created with above data to find max date and wrt. ID

ksrinivasan