Compare two tables for rate increase

[Trebor84]

Hi,

Hoping someone can assist with this please for a custom column. 

I have two tables, one with a list of staff, payment dates and amount, the other table has rate increase start date/end date if applicable and the increase amount. What i need to do is compare the tables and if the date in table 1 falls on or between the dates in table 2 then that employee gets an extra payment with the extra fee shown in a new column in table 1.

For example employee 12345 would get £50 total on 01/01/2021 but no extra payment on 01/08/2021.

If there is no end date in table 2 then the calculation needs to just check the increase start date with no limit on the end date.

Sample tables: https://easyupload.io/fcejon

Thanks

[Trebor84]

Hi,

They could appear in any order unfortunately due to issues with the source data. I could add an index column maybe to then do a count on job ref and return 1 for new job and zero if job exists previously on another row.

Then the bonus would only appear against a record with a 1 in in it?

[v-chenwuz-msft]

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.

[Trebor84 · Accepted solution]

Thanks, this works great.  Appreciate your help.

[Ashish_Mathur]

So please do so and share the revised file.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Trebor84]

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 

[Ashish_Mathur]

Hi,

How does one decide the order?  You said "preferably against the first record".  For employee id 12345, which is the first record.  Please give clear and consistent logic.