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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
aras409
Regular Visitor

How to reference a rate table to calculate wage in any given day

Hello everyone,

 

I have a master employee table where the position and pay rate for each employee is recorded for each time period. Each employee may have more than one Position or Pay Rate over time. Sample data as below:

NamePositionStart DateEnd DatePay Rate
AlexClerk2019-05-032020-01-05$16
BobClerk2019-06-082019-12-05$16
AlexCoordinator2020-01-062020-06-15$20
AlexManager2020-06-17 $25
BobAdmin2019-12-06 $18

 

Every month, the timecard information is received which contain the name, date, and number of hours worked. I was wondering how can I reference the master employee table to add the position and rate to the following Transaction List table:

NameDateHours WorkedPositionHourly RateTotal Pay
Alex2022-02-136???
Bob2022-02-133???
Alex2022-02-146???
Bob2022-02-143???
Alex2022-02-151???
Bob2022-02-156???
Alex2022-02-161???
Bob2022-02-168???

 

Thanks for the help!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@aras409 

Give this a try, add these as calculated columns in your second table.  This assumes that the first table is called 'Employees'.

Position = 
VAR _Name = [Name]
VAR _Date = [Date]
RETURN
CALCULATE ( 
    MAX ( Employees[Position] ),
    Employees[Name] = _Name,
    Employees[Start Date] <= _Date && ( Employees[End Date] >= _Date || ISBLANK ( Employees[End Date] ) ) 
)
Hourly Rate = 
VAR _Name = [Name]
VAR _Date = [Date]
RETURN
CALCULATE ( 
    MAX ( Employees[Pay Rate] ),
    Employees[Name] = _Name,
    Employees[Start Date] <= _Date && ( Employees[End Date] >= _Date || ISBLANK ( Employees[End Date] ) ) 
)
Total Pay = [Hours Worked] * [Hourly Rate]

I added some earlier dates in my sample to test the formulas.

jdbuchanan71_0-1654981047568.png

Important, if your employees are not unique (2 people named Bob working at the same time) you will get bad results.  It would be WAY better to use an employee_id field that is unique to each person.

 

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @aras409 
This is the sample file with the solution https://www.dropbox.com/t/IW6VeGycXU27VFs5

1.png2.png

Position = 
MAXX ( 
    FILTER (
        RELATEDTABLE ( 'Salaries' ),
        VAR WorkedDates = 'Worked Hours'[Date]
        VAR StartDate = Salaries[Start Date]
        VAR EndDate = COALESCE ( Salaries[End Date], TODAY ( ) )
        RETURN
            StartDate <= WorkedDates
                && EndDate >= WorkedDates
    ),
    Salaries[Position]
)
Hourly Rate = 
MAXX ( 
    FILTER (
        RELATEDTABLE ( 'Salaries' ),
        VAR WorkedDates = 'Worked Hours'[Date]
        VAR StartDate = Salaries[Start Date]
        VAR EndDate = COALESCE ( Salaries[End Date], TODAY ( ) )
        RETURN
            StartDate <= WorkedDates
                && EndDate >= WorkedDates
    ),
    Salaries[Pay Rate]
)
Total Pay = 'Worked Hours'[Hours Worked] * 'Worked Hours'[Hourly Rate]
jdbuchanan71
Super User
Super User

@aras409 

Give this a try, add these as calculated columns in your second table.  This assumes that the first table is called 'Employees'.

Position = 
VAR _Name = [Name]
VAR _Date = [Date]
RETURN
CALCULATE ( 
    MAX ( Employees[Position] ),
    Employees[Name] = _Name,
    Employees[Start Date] <= _Date && ( Employees[End Date] >= _Date || ISBLANK ( Employees[End Date] ) ) 
)
Hourly Rate = 
VAR _Name = [Name]
VAR _Date = [Date]
RETURN
CALCULATE ( 
    MAX ( Employees[Pay Rate] ),
    Employees[Name] = _Name,
    Employees[Start Date] <= _Date && ( Employees[End Date] >= _Date || ISBLANK ( Employees[End Date] ) ) 
)
Total Pay = [Hours Worked] * [Hourly Rate]

I added some earlier dates in my sample to test the formulas.

jdbuchanan71_0-1654981047568.png

Important, if your employees are not unique (2 people named Bob working at the same time) you will get bad results.  It would be WAY better to use an employee_id field that is unique to each person.

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.