How to find rate based on client, date and rate history

[Leah T.]

I'm loving PowerPivot, but admit that I still have my training wheels on! I'm attempting to get info that is beyond my experience of straightforward RELATED() and IF() formulas.

I need a calculated column in a PowerPivot table that returns a client's rate based on a date of contact and the start and end dates of a client's rate. Eventually, the rate will be added as a slicer in a pivot table report.

I have three tables in my PowerPivot window: [1] ContactHistory, [2] RateHistory and [3] ClientIdentifiers.

The ClientID columns in [1] ContactHistory and [2] RateHistory both have a relationship established with the ClientID column in [3] ClientIdentifiers.

The [1] ContactHistory table has rows containing the ClientID and Date_of_Contact.
The [2] RateHistory table has rows containing the ClientID, the ClientRate, Rate_Start_Date, and Rate_End_Date. Some Rate_End_Date(s) are null/blank because they are current, some clients have no start or end dates and/or are not on the RateHistory table ("unknown").

For clients for whom we do know the rate, I would like to calculate their rate at the Date_of_Contact in the [1] ContactHistory table.

Can someone point me in the right direction?

Thanks!

 

[powerpivotpro]

Hi Leah! Drop me a note at my email address (in my sig) and I will shoot you a workbook. If it solves your problem, I will post it as a solution on the blog

 

[powerpivotpro]

OK, the answer (and a sample workbook) is now posted:

http://www.powerpivotpro.com/2012/02/lookups-based-on-date-ranges/

Answer is confirmed by Leah to work for her, although she changed from AVERAGE() to MAX() in her version