You don't need a table calc here, just use MAX([Order Date]). You can also work out some date math to let you know if this is a new purchase:
IF MIN([Order Date]=MAX([Order Date]) THEN 'New Customer' ELSE 'Prior' END
What's your definition of 'Regained'?
I can get the "New" order status. It is the Regained customers that is the challenge right now. I need to be able to look at the Order Date prior to the Last order date to see if the last order was placed within the last 18 months.
So a regained customer is any account that has history with us but did not place any order with us since 18 months of the last order. I am trying to determine the MAX([Order Date]) -1 record back.
Were you able to figure this out? If yes, how did you go about it? I am have similar challenge.