PowerQuery/Dax - Valid Date Look-up

[happydays886]

Hi All,

I have two tables with the following information:

1. Rates
- Contract#
- WorkingClass
- RateofPay
- EffectiveDate

2. LaborDetail
- Contract#
- WorkingClass
- DateWorked
- HoursWorked

These are both Many-to-Many tables.

Somehow, I need to relate the two so that I can pull information on the labordetail table when the 'effectivedate' applies.
- Rateofpay pulled into the LaborDetail can only occur IF the 'DateWorked' is ">=" the 'effectivedate'
- The rate pulled also needs to correspond with the 'latest' effective date as the rates change with each 'new' effectivedate brought into the database

The problem is that there are several effective dates for each contract#+workingclass Unique ID (if that were build). So, forcing a relationship, ends up having a 'rateofpay' pulled into the LaborDetail table that ignores the rule of 'effectivedates'.

I'm stumped on this and could use all the help/guidance out there.

[happydays886]

Okay. After banging my head against a wall for the greater part of a few hours, I've come up with a solution.

In the Rates Table, I'll add a new column: "max" given the unique identifer "contract#+workingclass"; the "max" will pull the highest value on the effective date.

This is how I will resolve the issue of incorrect rates;

How i'll do this.. no clue.