Select values from the last applicable record

[#1]

Hi All,

I'm facing a particular problem, which is related to the godawful design of my source database (not me, I hasten to add!)

I have records that hold a numerical value (COST). Each record have VALIDFROM and VALIDTO date fields. I have to select a VALID date on my UI, and have the values where that date falls between VALIDFROM and VALIDTO displayed in a straight table. I managed to achieve this using a calculated dimension on my table where if this is true it results in a 1, if false it results in a null, and then I supress null values on this dimension.

Problem is the underlying DB and the application that populates that allows for multiple records to have the same or overlapping VALIDFROM and VALIDTO dates:

In this situation, my selected VALID date is 17/09/2014, so you can see both records fulfill that criteria (I'm doing the same filter on the BOOKINGFROM and BOOKINGTO fields too, as I want records that are valid and bookable on the same day). Both these records are valid, but I only want to display the COST from the last record (CONTRACTNUMBER 145).

I need all the records in my datamodel, as users can select any VALID date. If they were to select the date 01/10/2014, then only one record is applicable (CONTRACTNUMBER 140)

How can I, in a straight table, only select the COST from the last applicable record?

Any ideas?

Thanks,

Rory.