Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
teddy0bear
Frequent Visitor

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?
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1662522455459.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

2 REPLIES 2
teddy0bear
Frequent Visitor

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

Jihwan_Kim
Super User
Super User

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1662522455459.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.