Find the Highest Gift in a range that is specific to each set

[jcoleman13]

I am struggling to find a the Highest Gift in an 18 month period.  Here is the data set example.  

Each row is a gift and the date it was made.  I need to find the highest gift in an 18 month range.  So for ID 1, even though the $100 is the highest of the 4 gifts, its not the highest in an 18 month range.  The range starts from their last gift date and goes 18 months back... so ID 1's highest gift in their 18 month window is actually $50.  For ID 6 it's $45

ID	Gift Date	Gift Amount
1	1/1/1981	100
1	6/2/1997	25
1	12/4/1998	50
1	1/5/1999	25
2	1/1/2025	30
2	2/1/2025	30
2	3/1/2025	60
2	4/1/2025	30
3	1/6/2021	75
3	5/1/2021	75
3	8/7/2022	100
4	5/8/2024	100
5	9/2/2022	58
5	10/6/2023	25
6	12/9/2020	60
6	2/8/2023	45
6	5/1/2024	25

I used a summarize tool to get the most recent gift date, and then calculate 18 months from there... so i have a range... but i cant figure out how to pull between that range.  Any ideas?

[Zampy]

Since you have your date range, join the starting and end dates of that range back to your initial list using ID and then filter date to make sure it's between those two points.  After that, just take the highest value for your given ID.  

[jcoleman13]

But how do i filter it when the two dates vary between each row?

[Zampy · Accepted solution]

Using ID 1 as an example, your date range should be Max = 1/5/1999 and Min = 7/5/1998 if I math correctly.  After a join, your table would look like this:

ID	Gift Date	Gift Amount	Start Date	End Date
1	1/1/1981	100	7/5/1998	1/5/1999
1	6/2/1997	25	7/5/1998	1/5/1999
1	12/4/1998	50	7/5/1998	1/5/1999
1	1/5/1999	25	7/5/1998	1/5/1999

Then your filter would read as Start Date <= Gift Date  AND Gift Date <= End Date.  This would result in your table remaining as follows:

ID	Gift Date	Gift Amount	Start Date	End Date
1	12/4/1998	50	7/5/1998	1/5/1999
1	1/5/1999	25	7/5/1998	1/5/1999

And from there you can pull your Max Amount

[jcoleman13]

Thank you!!! That did it!