Apply a Rate

Hi Centaur 

You can create a measure to get the rate according to project ID and dates periods. Put this measure into Values of your pivot table. I create the following sample with Power BI. It's similar in Excel's Power Pivot table. 

m_Rate = 
var __date = MAX('data'[dte])
var __projID = MAX('data'[ProjID])
return
CALCULATE(MAX(Rates[Rate]),'Rates'[ProjectID]=__projID,'Rates'[Date From]<=__date,Rates[Date To]>__date)

Reference: 

Measures in Power Pivot (microsoft.com)

Excel Data Analysis Class 02: Power Pivot, DAX Formulas, Relationships, Data Modeling & Much More! - YouTube

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.