Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
But getting their rate I'm having issues with. Tried:
Solved! Go to Solution.
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 ()
)
)
VAR _addrate =
ADDCOLUMNS (
_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.
Ha-ha! I see where I screwed up. This works excellently, thank you!
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 ()
)
)
VAR _addrate =
ADDCOLUMNS (
_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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |