Employee Schedule

[_JohnDee_]

Hi everyone,

I need to schedule employee start and end by half-hour, depending on an expected and needed number

Here are my data :

I need to calculate three more columns :

"Start" : number of employees that need to start at each half hour to reach the expected number

"End" : number of employees that need to start at each half hour. A work day last 8:30 hour for each employee, so this is start + 8:30

"PresentEmployee" : number of employees present at each half-hour (depending on start and end)

here is the results that i expected :

Hre is the results that i have with the following calculated columns, and i d'ont understand why...

START

Start = 
VAR CurrentNum = 'EmployeeSchedule'[Numéro de la demi-heure]
VAR ExpectedEmployees = 'EmployeeSchedule'[ExpectedEmployees]
VAR EmployeesPresent = 
    CALCULATE(
        SUM('EmployeeSchedule'[ExpectedEmployees]),
        FILTER(
            'EmployeeSchedule',
            'EmployeeSchedule'[Numéro de la demi-heure] < CurrentNum
        )
    )
RETURN
MAX(0, ExpectedEmployees - EmployeesPresent)

END

End = 
VAR CurrentNum = 'EmployeeSchedule'[Numéro de la demi-heure]
VAR EndNum = CurrentNum - 17
RETURN
IF(
    EndNum > 0,
    CALCULATE(
        SUM('EmployeeSchedule'[Start]),
        FILTER(
            'EmployeeSchedule',
            'EmployeeSchedule'[Numéro de la demi-heure] = EndNum
        )
    ),
    0
)

PresentEmployees

PresentEmployees = 
VAR CurrentNum = 'EmployeeSchedule'[Numéro de la demi-heure]
RETURN
CALCULATE(
    SUM('EmployeeSchedule'[Start]) - SUM('EmployeeSchedule'[End]),
    FILTER(
        'EmployeeSchedule',
        'EmployeeSchedule'[Numéro de la demi-heure] <= CurrentNum
    )
)

 Thank you very much

[Rakesh1705]

Please check present employee formula. I have considered your formula only for the Present employee. If the overall solution is ok with you then please accept the same as the solution.

[Rakesh1705 · Accepted solution]

Source Data. Here expected employees figures are randomly taken.

To calculate the desired result I have added three additional columns named Duration,End Time and Max Employee

Start

End

Present Employees

If the same way out solves your problem then please accept this as your solution. Happy to help.

[_JohnDee_]

[update] it's ok, my actual formula for Present employees work perfectly [/update]

So great ! I think it's perfect !

But i can't the see Present employees colum formula. The last screenshot seems not to be the good one.

Thank you so much 🙂