Formula for the most recent date prior to last date

[Mix1325]

Hello,

I would appreciate if anyone can help with a formula that would solve the following task.

The last purchase of a material at a particular plant was made on 11/30/2018. There were also two other previous purchases (i.e., for the same material & plant) made on 8/25/2018 and 10/5/2018. I need a formula that would provide the most recent month and year prior to the last purchase. In the case provided above, the result should show Oct-18 (the date format needs to be the same as provided "Oct-18"). Help columns are allowed if needed. Please see attachment for the same example in excel.

Feel free to ask questions if additional details are needed. Thank you in advance for all your help and support in this matter.

 

[John Jairo V]

Hi, Mix1325!

You could try this formula:
[I3] : =TEXT(AGGREGATE(14,6,Table1[Purchase Date]/(Table1[Plant]=G3)/(Table1[Material]=H3),2),"mmm-yy")
or
[I3] : =TEXT(AGGREGATE(14,6,Table1[Purchase Date]/(Table1[Plant]&Table1[Material]=F3),2),"mmm-yy")

Blessings!

 

[Mix1325]

Thank you so much for your help with this, John! The formula works well.

Blessings!

 

[Mix1325]

John, I just realized that I forgot to include one of the most important criteria. If two purchases were made in the same month, the formula should show the purchase occurred prior to that month as opposed to listing the same month as the previous purchase. I have adjusted the file to show this situation.

 

[John Jairo V]

Hi, again!

In this case, you can use:

[I3] =TEXT(AGGREGATE(14,6,Table1[Purchase Date]/(Table1[Plant and Material]=F3)/(TEXT(Table1[Purchase Date],"my")<>TEXT(MAX(Table1[Purchase Date]*(Table1[Plant and Material]=F3)),"my")),1),"mmm-yy")

Blessings!

 

[Mix1325]

Thank you, John! This is exactly what I was looking for. Appreciate it!