Calculate employee inputs and outputs from an expected number of employees

[_JohnDee_]

Hello everyone,

I need your help in Power BI to calculate employee inputs and outputs.

For each half hour, I determine a number of incoming calls (INCOMING) and a number of outgoing calls (OUTGOING).

To these volumes, I apply an average processing time (200s for an incoming call and 150s for an outgoing call).

I thus determine a total processing time (TOTAL_TREATMENT_TIME) which I divide by 1800s (1800s in half an hour).

This allows me to calculate a number of employees (rounding up) capable of absorbing the call load (AGENT_NEEDED variable).

What I now need are two new columns that allow me to determine how many employees to clock in and out per half hour with the following constraints:

- 1 employee works nights and starts the day before at 10:30 p.m. and finishes at 6:30 a.m. So I must necessarily have an entry at 10:30 p.m. and an exit at 6:30 a.m. He's the only one who works over 2 days.

- For other employees, 1 entry must correspond to 1 exit, 8:30 hours later.

- the number of employees present (=entries - exits) must at least correspond to the expected (AGENT_NEEDED)

This is the expected result with fictitious data :

Here is the pbix file.

https://drive.google.com/file/d/1F4VzeJEOBTPxQmJCo6CDF3jBm-qOmv8R/view?usp=sharing

Thank you very much for your advices.

[_JohnDee_]

and then i need to determine automatically , when employee need to start.

So the blue column is the number of employees present for each half-hour.

Between 00:00 to 05:30 i have only 1 agent, it's the one who work at night and start at 22:30 on previous day.

At 06:00, an agent need to start beacause 2 agents are needed

At 06:30, i need 4 agents. It's the end for the night agent, 1 agent is already here (start at 06:00) so 3 agents must start at 06:30.

and so on until i reach the maximum of agents nedeed across all half-hours, 21 agents at 10:00.

And all agents who start must leave 8,5 hours later.

so this is the situation at this moment :

i can see that one agent is missing at 16:00 (11 vs 12 needed)

So i had to add one agent to start at 10:30 and finish at 19:00

and now, 3 agents are missing at 16:30 (7 vs 10 needed)

So i had to add 3 agents to start at 11:00 and finish at 19:30

and now, 2 agents are missing at 17:00 (7 vs 9 needed)

So i had to add 2 agents to start at 11:30 and finish at 20:00

And so one until the blue column is at least greater than AGENT_NEEDED column for each half-hour

[_JohnDee_]

incoming calls

outgoing calls

dimension table of half-hours

Then i calculate number of incoming and outcoming call.

Then i calcultate total time need to treat (200s for incoming and 150 for outgoing)

TOTAL_TREATMENT_TIME = sum(INCOMING_CALLS[INCOMING])*200+sum(OUTGOING_CALL[OUTGOING])*150

Then i calculated numbre of agent needed to treat all the calls by half-hour

AGENT_NEEDED = roundup([TOTAL_TREATMENT_TIME]/1800,0)

 (1800s in 1 half-hour) 

[Anonymous]

Hi @_JohnDee_ ,

Since I was unable to open the link to the pbix file you provided, I created some of my own data to fulfil your requirements.

Create MEASURE to count the number of employees that start during the period.

MEASURE =
VAR _pre =
SELECTEDVALUE ( 'Table 2'[Index] ) - 1
VAR _time =
CALCULATE (
MAX ( 'Table 2'[half_hour] ),
FILTER ( ALL ( 'Table 2' ), 'Table 2'[Index] = _pre )
)
VAR _count =
CALCULATE (
COUNT ( 'Table'[id] ),
FILTER (
ALL ( 'Table' ),
'Table'[start] <= MAX ( 'Table 2'[half_hour] )
&& 'Table'[start] > _time
)
)
RETURN
_count

The same logic applies to the count at the end, so I won't repeat it here. Also, I am unable to understand your logic in the blue column in the diagram and need further explanation from you.

Also, I hope you can share the data in some other way so that we can better help you with your problem.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[_JohnDee_]

I'm sorry if i wasn't clear.

I have not the start and end time for each employee.

I have 3 tables

- incoming calls by half-hours (to simplify beacause in reality i have a table for each call)

- outgoing calls by half hours (to simplify beacause in reality i have a table for each call)

- dimension table of half-hours 

Then for each half-hour, i determine the number of incoming and outgoing calls

Then i calculate a total time treatment for these calls : 

TOTAL_TREATMENT_TIME = sum(INCOMING_CALLS[INCOMING])*200+sum(OUTGOING_CALL[OUTGOING])*150

and after i calcultate a number of agent needed to treat this calls (1800s in one half-hour)

AGENT_NEEDED = roundup([TOTAL_TREATMENT_TIME]/1800,0)

And after that i would like to determine when agents need to start to be sure that i have enought people to treat the calls.

The blue column is the number of agent present for each half hour.

Between 00:00 to 06:30, i have 1 employee, the one who works at night and began at 22:30 on previous day.

At 06:00, a agent need to start because i need 2 persons to treat the call, so i have 2 agents present.

At 06:30, 3 agents start because 4 persons are needed. And i have the one who start at 06:00 but not the one who finish at 6:30.

And i had agent by half-hour until i reach the maximum of agents nedeed (21) at 10:00

But this agents who started leave 8,5 hours later.

So i need to had some other people on the next half-hours to be able to treat calls at the end of the day.

first step :

1 agent is missing at 16:00.

So i made 1 agent began at 10h30 and finish at 19:00

but 3 agents are now missing at 16h30.

So i had 3 agents at 11:00 and they finish at 19h30

and so on until the blue column is at least greater then the agent_needed column for each half-hour

Hope i am more clear.

Thank you, not easy to explain...

[_JohnDee_]

Hello and thank you for you answer. I will take time to look at your solution.

Here is a another test to share my pbix file, hope it will works this time : https://uploadnow.io/f/qnyvrBT

How did you do tho share directly your pbix file ? 🤔