Forum Discussion

teddy0bear's avatar
teddy0bear
Frequent Visitor
4 years ago
Solved

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

EmployeeStart DateEnd DatePay Code
Athos1/1/2022 A
Porthos1/1/2022 B
Aramis1/1/2022 C
Planchet1/1/20221/4/2022A

 

PayRates

Pay CodeStart DateEnd DateRate
A1/1/20221/3/202210
B1/1/202212/31/202220
C1/1/202212/31/202230
A1/4/202212/31/202220


Calendar

DateWorking Hours
1/1/20220
1/2/20220
1/3/20228

...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?
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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] )

2 Replies

  • teddy0bear's avatar
    teddy0bear
    Frequent Visitor
    3 years ago

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    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] )