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.

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors