Urgent! Capacity of Team per Month

[h11]

Hello,

We have a team of 18 employees and I would like to find the capacity of my team per month for the year (January - December) 2024 in the form of Line Chart.

Employee	StartDate	EndDate	Capacity
Aarav	8/1/2017	1/1/2018	121
Advait	8/1/2017	1/1/2030	121
Agastya	8/1/2018	7/1/2022	121
Sameer	5/1/2017	12/15/2019	121
Siddharth	5/18/2020	3/8/2021	40
Tanish	11/2/2020	4/22/2022	60
Uday	12/7/2020	4/22/2022	61
Varun	8/23/2021	11/13/2023	60
Vedant	11/29/2021	11/13/2023	61
Arjun	11/8/2021	12/16/2022	60
Aum	2/14/2022	12/16/2022	61
Ishan	10/17/2022	7/1/2023	121
Krish	9/12/2022	10/10/2030	60
Moksh	1/16/2023	10/10/2030	61
Nitin	10/9/2023	4/22/2024	80
Parin	4/3/2023	9/6/2030	60
Rishi	7/3/2023	5/15/2030	61
Shankar	4/22/2024	12/25/2034	121

Thanks!

[lbendlin · Accepted solution]

[h11]

@lbendlin Hi 

It's no. of hours allocated per month by an employee for a project.

[lbendlin]

Calendar table? Work week definition? Holidays?

How should Siddharth be accounted for in May 2020 and March 2021? How many hours for these months?

[h11]

@lbendlin Hi Sir, I guess I complicated the data. Let me simplify that for you. The job start date and job end dates are just their tenurity in the company. What I really want to know is a DAX measure to calculate the sum of capacity per month from January to December 2024 of the team who are currently in the company excluding the employees who left the company for example Sidharth left the company in 2021 so we don't need his capacity.  I want to show this data in the form of a line chart. 

[lbendlin]

So you only need this on month level? What about Ishan, should he count for the whole month of July 2023 even though he left on July 1st?

[h11]

@lbendlin Ishan can be ignored since he left the company in 2023. There are only 7 employees who are still with the company in 2024. I cannot make another table in power bi with 7 active ppl since this is a sample data and I have like 120+ employees table with a combination of active and left employees .I want a measure to calculate the sum of capacity of the ppl who are still in the company in only 2024. below table is for your reference to consider only the employees who are active.

For example : Jan 2024 has capacity of 443 since Shanker didn't join before April 2024. 

Employee	Job StartDate	Job EndDate	No of Hours employee is likely to spend per month for a project
Shankar	4/22/2024	12/25/2034	121
Krish	9/12/2022	10/10/2030	60
Moksh	1/16/2023	10/10/2030	61
Parin	4/3/2023	9/6/2030	60
Rishi	7/3/2023	5/15/2030	61
Advait	8/1/2017	1/1/2030	121
Nitin	10/9/2023	4/22/2024	80

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[lbendlin · Accepted solution]

[lbendlin]

What's the meaning of the Capacity number? Hopefully not hours per week?

Calendar table?  Work week definition? Holidays?