Find next oldest year in a range of records
I have a data table and on every records there is a date. The date can be the same for many records. As part of what I need to do, I've been required to find the most recent date, so I've simply used the formula Max(datefield) which works. I now need to find the next most recent date which I'm unsure how to do. An extract of data would look like this:
Amount | Date
100 | 31/01/2015
210 | 31/01/2015
450 | 31/01/2015
300 | 31/12/2014
My Maxdate formula returns 31/01/2015 and now I need to get a second formula to pick up the bottom row and pull through the 31/12/2014.
It's not simply a case of (Date-One month) because the dates can vary. I need FM to calculate the most recent date and from there check for the next most recent date, however long ago that may be.