Multi row Formula to identify max closed date

[Gabocorreia95]

Hi All 

The attached is a snippet of my whole dataset which has several accounts for the same id, what i'm trying to achieve is get a field that lets me know the latest closing date for all the accounts in the same ID group prior to the account in question's opened date  where the Relationship status = returning

~Thanks in advance for the help

[ChrisTX]

Can you provide expected output?  The requirement isn't clear for: how to determine "the account in question's opened date".

Chris

[Gabocorreia95]

Hi ChrisTX 

Apologies, so for the example provided the new column would only show the latest closed date for account 858002328 and 85003018 as their Relationship status at opened date is "returning" 

The new column for 85002328 would show 2021/01/29 as that's the  latest closed date prior to 85002328  open date which is 2021/02/06

For 85003018 The new column would show 2022/12/07   as that's the  latest closed date prior to  85003018  open date which is 2024/01/04

Hopefully the above makes sense. 

Thanks in advance for your help

[Raj]

please provide some clarity on requirement as it is difficult to understand the required output.
are you looking for ID opening date and latest closing date for "returning" or is there something else.

are you looking for this,
ID Opening Closing
316 2015-10-09 2024-01-18

[Gabocorreia95]

Hi Raj 

Basically I'm looking for the latest closed date of the accounts in the same ID group prior to the open date of the accounts where the  Relationship status at opened date is equal to returning.

Please refer to the example I shared on my previous response, hopefully that will help clarify my question. 

Many thanks

[ChrisTX]

Can you provide sample input data and expected output using the Table format in this post?

[Gabocorreia95]

Hi ChrisTX 

Please find attached an example of the desired output

Many thanks

[AndrewDMerrill · Accepted solution]

Here is a workflow that I believe does what you're looking for (as a note include actual tables with sample input is far more helpful than images of data):

Hope this helps! Happy Solving!!!