Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to Solution.
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.
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]
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.
User | Count |
---|---|
141 | |
71 | |
70 | |
54 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |