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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anjbauer1993
Frequent Visitor

Calculating daily hours with changing pay rates

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.idemployee.idPay RateEffective Date - Pay
1103259671501/18/2019
1103259679012/31/2018
11199552501/9/2019
1119955232.55/5/2013
108910278201/24/2019
1089102787012/5/2017
1171392661001/12/2019
1171392666311/18/2018

 

 

 

timesheets

Job IDDateHours
117112/31/20188
111912/31/20188
110312/31/20188
108912/31/20186
11031/1/20198
11191/1/20198
11711/1/20190
10891/1/20192
11031/2/20198
10891/2/20198
11711/2/20198
11191/2/20198
11031/3/20198
10891/3/20198
11711/3/20198
11191/3/20198
11031/4/20198
10891/4/20198
11711/4/20198
11191/4/20198
11031/5/20190
11711/5/20190
11191/5/20198
10891/5/20194
11191/6/20190
11031/6/20190
11711/6/20190
10891/6/20192
11031/7/20198
10891/7/20198
11711/7/20198
11191/7/20191
11031/8/20198
11711/8/20198
11191/8/20198
10891/8/20196
11191/9/20198
11031/9/20198
11711/9/20198
10891/9/20196
11711/10/20198
11031/10/20198
10891/10/20198
11191/10/20198
11031/11/20198
10891/11/20198
11711/11/20198
11191/11/20198
11711/12/20190
11191/12/20198
11031/12/20190
10891/12/20192
11711/13/20190
11191/13/20190
11031/13/20190
10891/13/20192
11711/14/20198
11031/14/20198
10891/14/20198
11191/14/20190
11031/15/20198
10891/15/20198
11711/15/20198
11191/15/20198
11191/16/20198
11031/16/20198
11711/16/20198
10891/16/20194
11031/17/20198
11711/17/20198
11191/17/20198
10891/17/20196
11031/18/20198
10891/18/20198
11711/18/20198
11191/18/20198
11191/19/20198
11031/19/20190
11711/19/20190
10891/19/20194
11031/20/20190
11711/20/20190
11191/20/20190
10891/20/20192
11031/21/20198
10891/21/20198
11191/21/20198
11711/21/20190
11711/22/20198
11191/22/20198
11031/22/20198
10891/22/20196
11711/23/20198
11031/23/20198
10891/23/20198
11191/23/20198
11031/24/20198
11711/24/20198
11191/24/20198
10891/24/20196
11031/25/20198
11711/25/20198
11191/25/20198
10891/25/20196
11711/26/20190
11191/26/20198
11031/26/20190
10891/26/20192
11191/27/20190
11031/27/20190
11711/27/20190
10891/27/20194
11031/28/20198
11711/28/20198
11191/28/20190
10891/28/20196
11711/29/20198
11031/29/20198
10891/29/20198
11191/29/20198
10891/30/20198
11711/30/20198
11031/30/20198
11191/30/20198
11711/31/20198
11031/31/20198
10891/31/20198
11191/31/20198
11032/1/20198
11712/1/20198
11192/1/20198
10892/1/20196

 

 

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.
1 ACCEPTED SOLUTION
anjbauer1993
Frequent Visitor

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
)

View solution in original post

1 REPLY 1
anjbauer1993
Frequent Visitor

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
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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