March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
108 | |
73 | |
54 | |
52 | |
44 |
User | Count |
---|---|
161 | |
112 | |
67 | |
60 | |
50 |