finding the correct row (not the last row) in a scd2 table

[HandsFree]

Hi, I'm new to PP/DAX so I'm hoping someone can help me here. I'm working with customer data so I'll have to be a bit vague on the details but the general idea is this:
There's an 'hours' table that has a line for each date an employee has worked, with ao columns for [EmpoyeeID], [Date], [Hours].
There's an 'employee' table with multiple rows per employee with [Rate], [Startdate], [Enddate]. This table is created manually so we can change this if needed.

Now users should be able to create their own pivot tables without having to deal with finding the correct rate, so I want to add a column [Rate] to the 'hours' table that holds the correct rate for the employee/date as specified in the 'employee' table.
Since both tables don't have unique values there's no relation possible. Note that this is not about finding the 'current' rate, but the rate on any given date in the 'hours' table.
I tried using Lookupvalue, but that didn't work. I assume that's because of this restriction on searchvalue: "A scalar expression that does not refer to any column in the same table being searched."
Any ideas on how to do this?
thanks

BTW there's also a 'calendar' table but it's not been used so far.

 

[MD610]

I believe I have solutions for this using either a Calc Column or a Measure depending on your needs.

First, does your Rates table have records with no EndDate since the current Rates probably don't have an EndDate yet? If so, you need one Calc Column in your Rate table to give an upper limit in the distant future.

You could use something like this:
Rates[End2] =IF(ISBLANK(Rates[EndDate]), DATE(2099,12,31), Rates[EndDate])

Then if you want to add a Calc Column to the Hours table, use something like this:
Hours[Rate Calc Column] = CALCULATE(SUM(Rates[Rates]), FILTER('Rates', 'Rates'[StartDate]<='Hours'[Date] && 'Rates'[End2]>='Hours'[Date] &&'Rates'[Employee] ='Hours'[Employee]))

If you want a Measure that will work in a Pivot, use this:
Correct Rate Measure:=IF(HASONEVALUE('Hours'[Date]) && HASONEVALUE('Hours'[Employee]),CALCULATE(SUM(Rates[Rates]), FILTER('Rates', 'Rates'[StartDate]<=VALUES('Hours'[Date]) && 'Rates'[End2]>=VALUES('Hours'[Date]) &&'Rates'[Employee] =VALUES('Hours'[Employee]))),BLANK())

Then, in your Pivot, use the Date and Employee from Hours in your Rows. Finally, either use [Correct Rate Measure] or the sum of the Calc Column - [Sum of Rate Calc Column] and you should get the correct results. Note that using the Measure will not give subtotal values for the Date because of the need for HASONEVALUE(). Using the [Sum of Rate Calc Column] will give you subtotals for the dates in your pivot, so depending on your needs, one may be better than the other.

Also, I am assuming that the end date for one employee's rate does not share the same start date as that same employee's next rate. So if Rate 1 for Bill ends on 1/1/2013, I assume that Rate 2 for Bill begins on 1/2/2013 and not 1/1/2013. If they do share dates, you will have to adjust the greater than and less than as needed for your desired result.

 

[MD610]

Sorry, just realized that I referred to your 'Employee' table as the 'Rates' table throughout my post. Just substitute your table name for mine.

 

[Laurent C]

Slightly modifying MD610's calculated column expression, you could do:
Hours[Rate Calc Column] :=
CALCULATE( VALUES(Employees[Rates])
, FILTER('Employees'
, [StartDate]<= 'Hours'[Date]
&& ( 'Hours'[Date] < '[EndDate]
|| ISBLANK( [End Date]
)
&&'Employees'[Employee] ='Hours'[Employee])
)
In that case, the expression will return an error, if you have overlaps in your Employees table.

If you want to calculate the expression without using an [End Date] column, check my post on this lookup scenario:
Equivalent of VLOOKUP in DAX – Part II – using TOPN | The Data Specialist

 

[HandsFree]

Thanks, it works!
All the end dates have a value so I don't need the checks on blanks though.