Apply rate that is between 2 dates

lekkerbek

You can import the rate into the fact table.

First you need to replace the blank values (with Power Query) within the Rates table (column End Date) with a big date (for example 12/31/9999).

Then you can add a column in the fact table with this DAX:

Rate = 
CALCULATE(
    VALUES( Rates[InternalRate] ),
    Rates[StartDate] <= EARLIER(Data[Date]),
    Rates[EndDate] >= EARLIER(Data[Date]),
    Rates[EmployeeID] = EARLIER(Data[EmployeeID])
)

then your projection costs can be calculated on the fly without needing to add a calculated column for those. I would recommend to add the calculated column with the Rate as I did and not multiply it by Quantity within the calculated column in order to get a better compression (Rates have less unique values). If you're extracting data from a database then you can join the fact table to the Rates table in order to retrieve the correct rate:

SELECT FactTable.*, Rates.Rate

FROM FactTable INNER JOIN Rates

ON FactTable.EmployeeID = Rates.EmployeeID AND FactTable.Date BETWEEN Rates.StartDate and Rates.EndDate

Another alternative can also be to create the below model and not add the calculated column with the Rate but retrieve at query time but I think you will get a poorer performance so I'd go with the first option.