Compare two tables for rate increase

Hi Trebor84 

You ware right, use the index to identify the first rows.

First, add an index column in power query editor and use the following expression will get the result:

Column =
VAR _first =
    MINX(
        FILTER(
            'Table 1',
            [Employee ID] = EARLIER( 'Table 1'[Employee ID] )
                && [Job Ref] = EARLIER( 'Table 1'[Job Ref] )
        ),
        [Index]
    )
VAR _nolimit =
    IF(
        RELATED( 'Table 2'[Increse End Date] ) = BLANK(),
        DATE( 2099, 1, 1 ),
        RELATED( 'Table 2'[Increse End Date] )
    )
RETURN
    IF(
        [Index] = _first,
        IF(
            RELATED( 'Table 2'[Increase Start Date] ) <= [Date]
                && _nolimit >= [Date],
            RELATED( 'Table 2'[Bonus] ),
            BLANK()
        ),
        BLANK()
    )

result:

I put the pbix file in the end you can refer

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.