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

Be 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

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!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.