Nick,
Let me break it down this way.
Data Table (main columns below)
EmployeeNumber AlphaName WorkDate PayType Hours JobType JobLevel
This table will contain all the hours worked by our employees. I have created a new column in this table combining Job type and Level into one column.
Rate Table (main columns below)
Start End JobType JobLevel BaseHourlyRate ComponentA ComponentB BillableRate
Similar to Data Table, I have created a new column combining Job type and Level into one column.
Each singular row identifies the job type and level and the start and end dates that row.
For example:
Start End Job Type / Level Base Rate Component A Component B Billable Rate
3/1/2019 3/15/2019 xyz-123 $20.00 $2.00 $3.00 $25.00
3/16/2019 10/31/2099 xyz-123 $22.00 $2.20 $3.30 $27.50
As there can be multiple lines with the same Job Type / Level, I cannot connect the Data Table to the Rate table due to many to many relationships.
I am attempting via a measure to take the hours incurred by Workdate in the Data table, reach into the Rate Table, find the applicable row where based on the Workdate it finds the base rate that was in effect on that date. Additional calculations with the same logic can then occur on determining Billable rates, as well as the sum of components A and component B.
I attempted the formula below but it does not filter the Rate Table correctly to find only the jobtype / level that applies to the WorkDate period.
Base $ = CALCULATE(
SUMX('Data Table',' Data Table'[Hours]*
LOOKUPVALUE('RATES'[Base Rate ],RATES[Job Type/ Level],'Data Table'[Job Type / Level])),
FILTER('Data Table','Data Table'[Pay Type]="1"),
FILTER('Data Table',
'Hours Table'[Work Date ]>=MIN('RATES'[Start]) &&
'Hours Table'[Work Date ]<=MAX('RATES'[End])))
Does this help? Thanks Again.