cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Predicting Employee Cost based on Calendar

I'm attempting to figure out how much an employee would cost if they worked full time based on employee start/end dates and pay rates start/end dates against a calendar rather then something like a timesheet.

Employees

 Employee Start Date End Date Pay Code Athos 1/1/2022 A Porthos 1/1/2022 B Aramis 1/1/2022 C Planchet 1/1/2022 1/4/2022 A

PayRates

 Pay Code Start Date End Date Rate A 1/1/2022 1/3/2022 10 B 1/1/2022 12/31/2022 20 C 1/1/2022 12/31/2022 30 A 1/4/2022 12/31/2022 20

Calendar

 Date Working Hours 1/1/2022 0 1/2/2022 0 1/3/2022 8

...etc

Paycodes

 Pay Code A B C

In this case, Plachet's hours should drop off from the 5th onward and Athos and Plachet's hours should change on the 4th.
I can count how many people are working with this measure:

MuskCount =
CALCULATE(COUNTROWS(Employees),
filter(VALUES(Employees[Start Date]), Employees[Start Date]<= max('Calendar'[Date])),
filter(values(Employees[End Date]), or(Employees[End Date] >= min('Calendar'[Date]), isblank(Employees[End Date]))))

But getting their rate I'm having issues with. Tried:

MuskRate =
CALCULATE(
sum(PayRates[Rate]),
filter(
PayRates,
PayRates[Pay Code] = Employees[PayCode]
),
FILTER(PayRates,
PayRates[Start Date]<=MAX('Calendar'[Date]) && PayRates[End Date] >= MIN('Calendar'[Date])
)) * [Working Hours]

The plan was to get the rates working and then filter by employee start/end dates but even that doesn't seem to be working. It shows values for 1/1/2022 for everyone correctly and it shows Athos and Planchet's hours on 1/4/2022 correctly but everything else is blank. Not sure what I'm doing wrong, probably something obvious. Normally I could do this with lookup columns and timesheet data but the calendar is screwing me up since I can't exactly have a mini calendar per 100+ employees. Any ideas how to make this work with measures?
1 ACCEPTED SOLUTION
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Payments measure: =
VAR _workinghrs = [Working Hours:]
VAR _employeetable =
FILTER (
Employee,
Employee[Start Date] <= MAX ( 'Calendar'[Date] )
&& OR (
Employee[End Date] >= MIN ( 'Calendar'[Date] ),
Employee[End Date] = BLANK ()
)
)
_employeetable,
"@Rate",
MAXX (
FILTER (
PayRates,
PayRates[Pay Code] = EARLIER ( Employee[Pay Code] )
&& PayRates[Start Date] <= MAX ( 'Calendar'[Date] )
&& PayRates[End Date] >= MIN ( 'Calendar'[Date] )
),
PayRates[Rate]
),
"@workinghrs", _workinghrs
)
RETURN
SUMX ( _addrate, [@workinghrs] * [@Rate] )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Frequent Visitor

Ha-ha! I see where I screwed up. This works excellently, thank you!

Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

``````Payments measure: =
VAR _workinghrs = [Working Hours:]
VAR _employeetable =
FILTER (
Employee,
Employee[Start Date] <= MAX ( 'Calendar'[Date] )
&& OR (
Employee[End Date] >= MIN ( 'Calendar'[Date] ),
Employee[End Date] = BLANK ()
)
)
_employeetable,
"@Rate",
MAXX (
FILTER (
PayRates,
PayRates[Pay Code] = EARLIER ( Employee[Pay Code] )
&& PayRates[Start Date] <= MAX ( 'Calendar'[Date] )
&& PayRates[End Date] >= MIN ( 'Calendar'[Date] )
),
PayRates[Rate]
),
"@workinghrs", _workinghrs
)
RETURN
SUMX ( _addrate, [@workinghrs] * [@Rate] )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.