Calculating daily hours with changing pay rates

[anjbauer1993]

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

Total Hours =

IF( CALCULATE (

SUM ( timesheets[Hours] ),

CROSSFILTER ( dim_job[job.id], fact_job[job.id], BOTH )

) = 0, blank(),CALCULATE (

SUM ( timesheets[Hours] ),

CROSSFILTER ( dim_job[job.id], fact_job[id], BOTH )))

Pay Rate =

VAR effectiveDate =

CALCULATE (

LASTDATE ( fact_job[Effective Date - Pay] ),

FILTER (

fact_job,

( fact_job[Effective Date - Pay] <= SELECTEDVALUE ( dim_Date[Date] ) )

)

)

RETURN

CALCULATE (

LASTNONBLANK ( fact_job[Pay Rate], 1 ),

fact_job[Effective Date - Pay] = effectiveDate

)

Employee Pay =

[Total Hours] * [Pay Rate]

The Total Hours and Employee Pay measures both work fine, but I haven't been able to the Pay Rate measure to work. I appreciate any help, I've been banging my head against it for a few days now.

[anjbauer1993 · Accepted 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
)