Forum Discussion

teddy0bear's avatar
teddy0bear
Frequent Visitor
3 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 t...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 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] )
Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 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] )