Get the latest value based on its "date" related field


I will try to make it as simple as possible:
We are in the Property management industry, where rent amount is calculated based on the latest value of an index of a given quarter. A rent has a base quarter (Q1 or Q2 or Q3 or Q4) at year N which is used for the calculation of this new rent. When the new “base” quarter index is published (at N+1) then the rent value is updated. Indexes, in general, are updated on a quarterly basis (and are roughly 2 quarters behind based on today’s date). There are 4 indexes, each rent uses only one of them. The index value usually goes up but can go down (in a economy slow-down situation).
My challenge is to automatically calculate the new value of a rent when it’s base quarter index is updated at N+1.

Current setup:
I have a table with all rents, their related index, their base quarter index, the initial rent value (base quarter year N), and this table should host the updated yearly rent
I have a table with quarterly values of each index, with the reference column is the date of publication of the index (Q1-20XX), and in columns the values of each index.
I have a table with the four quarters (unlink to years) where I hope to identify the "latest quaterly value of each index.

I need to extract this latest index value (it is not the MAX index value as the index value can go down) based on the quarter “date”.

What I did so far:
I managed to do a manual scenario where I identify the “last quarter” (for each index) with a checkbox and rollup the max value of these boxes checked, but it is NOT satisfactory long term.

I’d like to find the automated solution to this calculation.

Anyone can help?
Thanks tons!

I wrote a script to lookup the first/last linked value in a linked records field for a contest being judged this week.

If you find the script helpful, I’d appreciate the feedback on the contest page, as I believe that community usefulness is taken into account in the judging.