Predicting Employee Cost based on Calendar

[teddy0bear]

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?

[teddy0bear]

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

[Jihwan_Kim · Accepted 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] )