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

[aras409]

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:

Name	Position	Start Date	End Date	Pay Rate
Alex	Clerk	2019-05-03	2020-01-05	$16
Bob	Clerk	2019-06-08	2019-12-05	$16
Alex	Coordinator	2020-01-06	2020-06-15	$20
Alex	Manager	2020-06-17		$25
Bob	Admin	2019-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:

Name	Date	Hours Worked	Position	Hourly Rate	Total Pay
Alex	2022-02-13	6	?	?	?
Bob	2022-02-13	3	?	?	?
Alex	2022-02-14	6	?	?	?
Bob	2022-02-14	3	?	?	?
Alex	2022-02-15	1	?	?	?
Bob	2022-02-15	6	?	?	?
Alex	2022-02-16	1	?	?	?
Bob	2022-02-16	8	?	?	?

Thanks for the help!

[tamerj1]

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

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 · Accepted solution]

@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.

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.