Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Trebor84
Helper II
Helper II

Compare two tables for rate increase

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

1 ACCEPTED SOLUTION

Thanks, this works great.  Appreciate your help.

View solution in original post

7 REPLIES 7
Trebor84
Helper II
Helper II

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?

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:

vchenwuzmsft_0-1636694889175.png

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.

Thanks, this works great.  Appreciate your help.

So please do so and share the revised file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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,

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.