Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm currently working on a payroll report with multiple employees in multiple roles. I have a table of timesheets and a table with job information. Connecting the two tables has not been an issue, but the trouble that I'm having is that the pay rates are not constant. I have tried a few different methods of getting accurate pay amounts but so far I have been unsuccessful. Below are some simplified versions of my tables and the measures that I've tried.
dim_job
job.id |
1103 |
1119 |
1089 |
1171 |
fact_job
job.id | employee.id | Pay Rate | Effective Date - Pay |
1103 | 25967 | 150 | 1/18/2019 |
1103 | 25967 | 90 | 12/31/2018 |
1119 | 9552 | 50 | 1/9/2019 |
1119 | 9552 | 32.5 | 5/5/2013 |
1089 | 10278 | 20 | 1/24/2019 |
1089 | 10278 | 70 | 12/5/2017 |
1171 | 39266 | 100 | 1/12/2019 |
1171 | 39266 | 63 | 11/18/2018 |
timesheets
Job ID | Date | Hours |
1171 | 12/31/2018 | 8 |
1119 | 12/31/2018 | 8 |
1103 | 12/31/2018 | 8 |
1089 | 12/31/2018 | 6 |
1103 | 1/1/2019 | 8 |
1119 | 1/1/2019 | 8 |
1171 | 1/1/2019 | 0 |
1089 | 1/1/2019 | 2 |
1103 | 1/2/2019 | 8 |
1089 | 1/2/2019 | 8 |
1171 | 1/2/2019 | 8 |
1119 | 1/2/2019 | 8 |
1103 | 1/3/2019 | 8 |
1089 | 1/3/2019 | 8 |
1171 | 1/3/2019 | 8 |
1119 | 1/3/2019 | 8 |
1103 | 1/4/2019 | 8 |
1089 | 1/4/2019 | 8 |
1171 | 1/4/2019 | 8 |
1119 | 1/4/2019 | 8 |
1103 | 1/5/2019 | 0 |
1171 | 1/5/2019 | 0 |
1119 | 1/5/2019 | 8 |
1089 | 1/5/2019 | 4 |
1119 | 1/6/2019 | 0 |
1103 | 1/6/2019 | 0 |
1171 | 1/6/2019 | 0 |
1089 | 1/6/2019 | 2 |
1103 | 1/7/2019 | 8 |
1089 | 1/7/2019 | 8 |
1171 | 1/7/2019 | 8 |
1119 | 1/7/2019 | 1 |
1103 | 1/8/2019 | 8 |
1171 | 1/8/2019 | 8 |
1119 | 1/8/2019 | 8 |
1089 | 1/8/2019 | 6 |
1119 | 1/9/2019 | 8 |
1103 | 1/9/2019 | 8 |
1171 | 1/9/2019 | 8 |
1089 | 1/9/2019 | 6 |
1171 | 1/10/2019 | 8 |
1103 | 1/10/2019 | 8 |
1089 | 1/10/2019 | 8 |
1119 | 1/10/2019 | 8 |
1103 | 1/11/2019 | 8 |
1089 | 1/11/2019 | 8 |
1171 | 1/11/2019 | 8 |
1119 | 1/11/2019 | 8 |
1171 | 1/12/2019 | 0 |
1119 | 1/12/2019 | 8 |
1103 | 1/12/2019 | 0 |
1089 | 1/12/2019 | 2 |
1171 | 1/13/2019 | 0 |
1119 | 1/13/2019 | 0 |
1103 | 1/13/2019 | 0 |
1089 | 1/13/2019 | 2 |
1171 | 1/14/2019 | 8 |
1103 | 1/14/2019 | 8 |
1089 | 1/14/2019 | 8 |
1119 | 1/14/2019 | 0 |
1103 | 1/15/2019 | 8 |
1089 | 1/15/2019 | 8 |
1171 | 1/15/2019 | 8 |
1119 | 1/15/2019 | 8 |
1119 | 1/16/2019 | 8 |
1103 | 1/16/2019 | 8 |
1171 | 1/16/2019 | 8 |
1089 | 1/16/2019 | 4 |
1103 | 1/17/2019 | 8 |
1171 | 1/17/2019 | 8 |
1119 | 1/17/2019 | 8 |
1089 | 1/17/2019 | 6 |
1103 | 1/18/2019 | 8 |
1089 | 1/18/2019 | 8 |
1171 | 1/18/2019 | 8 |
1119 | 1/18/2019 | 8 |
1119 | 1/19/2019 | 8 |
1103 | 1/19/2019 | 0 |
1171 | 1/19/2019 | 0 |
1089 | 1/19/2019 | 4 |
1103 | 1/20/2019 | 0 |
1171 | 1/20/2019 | 0 |
1119 | 1/20/2019 | 0 |
1089 | 1/20/2019 | 2 |
1103 | 1/21/2019 | 8 |
1089 | 1/21/2019 | 8 |
1119 | 1/21/2019 | 8 |
1171 | 1/21/2019 | 0 |
1171 | 1/22/2019 | 8 |
1119 | 1/22/2019 | 8 |
1103 | 1/22/2019 | 8 |
1089 | 1/22/2019 | 6 |
1171 | 1/23/2019 | 8 |
1103 | 1/23/2019 | 8 |
1089 | 1/23/2019 | 8 |
1119 | 1/23/2019 | 8 |
1103 | 1/24/2019 | 8 |
1171 | 1/24/2019 | 8 |
1119 | 1/24/2019 | 8 |
1089 | 1/24/2019 | 6 |
1103 | 1/25/2019 | 8 |
1171 | 1/25/2019 | 8 |
1119 | 1/25/2019 | 8 |
1089 | 1/25/2019 | 6 |
1171 | 1/26/2019 | 0 |
1119 | 1/26/2019 | 8 |
1103 | 1/26/2019 | 0 |
1089 | 1/26/2019 | 2 |
1119 | 1/27/2019 | 0 |
1103 | 1/27/2019 | 0 |
1171 | 1/27/2019 | 0 |
1089 | 1/27/2019 | 4 |
1103 | 1/28/2019 | 8 |
1171 | 1/28/2019 | 8 |
1119 | 1/28/2019 | 0 |
1089 | 1/28/2019 | 6 |
1171 | 1/29/2019 | 8 |
1103 | 1/29/2019 | 8 |
1089 | 1/29/2019 | 8 |
1119 | 1/29/2019 | 8 |
1089 | 1/30/2019 | 8 |
1171 | 1/30/2019 | 8 |
1103 | 1/30/2019 | 8 |
1119 | 1/30/2019 | 8 |
1171 | 1/31/2019 | 8 |
1103 | 1/31/2019 | 8 |
1089 | 1/31/2019 | 8 |
1119 | 1/31/2019 | 8 |
1103 | 2/1/2019 | 8 |
1171 | 2/1/2019 | 8 |
1119 | 2/1/2019 | 8 |
1089 | 2/1/2019 | 6 |
Solved! Go to Solution.
I was able to solve this by tying the Pay Rate to the date from the timesheet instead of the date dimension
Pay Rate =
VAR effectiveDate =
CALCULATE (
LASTDATE ( Placements[Effective Date - Pay] ),
FILTER (
fact_job,
( fact_job[Effective Date - Pay] <= SELECTEDVALUE ( timesheets[Date] ) )
)
)
RETURN
CALCULATE (
SELECTEDVALUE ( fact_job[Pay Rate], 1 ),
fact_job[Effective Date - Pay] = effectiveDate
)
I was able to solve this by tying the Pay Rate to the date from the timesheet instead of the date dimension
Pay Rate =
VAR effectiveDate =
CALCULATE (
LASTDATE ( Placements[Effective Date - Pay] ),
FILTER (
fact_job,
( fact_job[Effective Date - Pay] <= SELECTEDVALUE ( timesheets[Date] ) )
)
)
RETURN
CALCULATE (
SELECTEDVALUE ( fact_job[Pay Rate], 1 ),
fact_job[Effective Date - Pay] = effectiveDate
)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |