Compare two tables for rate increase

Hi,

Thanks, this works great.  One thing I forgot to mention though was that I also need to take into account a job reference number field as an employee may be paid a second fee for the same job reference.  In the attached example employee 12345 received two payments for job ABC123 but the bonus should only be paid once and preferably against the first record.  If there are duplicate job refs then they will always be the same date and unique to just that employee so job ABC123 would never apply to another employee for example.

Sample file: https://easyupload.io/8ft38c

Thanks 

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.

So please do so and share the revised file.