Find overlapping date ranges and add corresponding performance metrics to measure efficiency

[Anonymous]

Employee Booking Type  Jobs WorkLoad% Start Date  End date
John Chargeable CNS 20 04/11/2019 31/01/2020
John Chargeable CNS 20 04/05/2020 27/11/2020
John Chargeable Integrated LLC 100 01/10/2019 31/12/2019
John Chargeable BCS 100 14/10/2019 01/11/2019
John Vacation/Holiday SN 100 30/09/2019 11/10/2019
John Vacation/Holiday Annual leave 100 23/12/2019 24/12/2019
John Chargeable Tech PLC 50 29/07/2019 30/09/2019
John Chargeable Integrated LLC 50 29/07/2019 30/09/2019
John Chargeable Accenture Pte Ltd  80 16/03/2020 15/05/2020
John Vacation/Holiday SN 100 10/01/2020 10/01/2020
John Chargeable CITY 100 23/03/2020 30/07/2020
John Chargeable SiH 50 20/01/2020 20/03/2020
John Chargeable CNS 20 03/02/2020 20/03/2020
John Chargeable CDLHT 30 22/01/2020 20/03/2020
Bernard Chargeable BCA 100 01/07/2019 30/09/2019
Bernard Chargeable Ecquaria 100 07/10/2019 28/02/2020
Bernard Vacation/Holiday Annual leave 100 22/01/2020 30/01/2020
Bernard Vacation/Holiday Annual leave 100 13/08/2019 16/08/2019
Bernard Chargeable SiH 100 01/10/2019 04/10/2019
Bernard Chargeable PSD 100 01/10/2019 04/10/2019
Bernard Vacation/Holiday Family Care 100 05/05/2020 06/05/2020
Bernard Chargeable UOI 100 02/03/2020 27/03/2020
Bernard Chargeable Ecq 100 30/03/2020 30/07/2020
Bernard Chargeable Sun 100 02/03/2020 27/03/2020
Bernard Chargeable Sun 50 30/03/2020 10/04/2020

Employee A and B have a list of jobs for various time periods.

To track Person A and Person B's efficiency for any time period we have a Loading system.

Each job has a certain Loading percentage. Employee can be put on multiple jobs, however the combined Chargeable loading cannot exceed 100. 

If the jobs are plotted on a gantt chart

Picture above shows only the chargeable jobs, Top-left job's loading is 50, Middle job's loading is 20, Bottom job's loading is 30.

Total is 100 which is fine

However if Top-right job's loading is 80, Middle job's loading is 20, Bottom job's loading is 30.

Total loading for the Top-right job's and bottom two job's is 130, which is overloaded for that job.

I want to go through each job and calculate if it ever overlaps other Chargeable jobs and what is the total loading for that job + other overlapped jobs.

Then in a new column:

Overloaded (for Chargeable  loading > 150)

Underloaded (for Chargeable  loading < 100)
Normal (Chargeable loading = 100 or the row contains non-Chargeable  jobs)

This is what i've tried:

Suppose the table is,

Name 	Booking Type 	Job	Loading	Start Date 	End date
John	Chargeable	job1	20	04/11/2019	31/01/2020
John	Chargeable	job2	100	01/10/2019	31/12/2019
John	Chargeable	job3	100	14/10/2019	01/11/2019
John	Chargeable	job6	50	29/07/2019	30/09/2019
John	Chargeable	job7	50	29/07/2019	30/09/2019
John	Vacation/Holiday	job8	100	10/01/2020	10/02/2020
John	Chargeable	job9	100	23/03/2020	30/07/2020
John	Chargeable	job10	50	20/01/2020	20/03/2020
John	Chargeable	job11	20	03/02/2020	20/03/2020
John	Chargeable	job12	30	22/01/2020	20/03/2020
Kimberly	Chargeable	job13	20	03/02/2020	20/03/2020
Kimberly	Chargeable	job14	100	22/01/2020	20/03/2020
Kimberly	Chargeable	job15	100	22/04/2020	20/04/2020

I created a new column using this dax formula,

loading Status = 
VAR staffname = 'Staff Booking'[Name ]
    VAR jobstart = 'Staff Booking'[Start Date].[Date]
    VAR jobend = 'Staff Booking'[End Date].[Date]
    VAR bookingtype = 'Staff Booking'[Booking Type ]
	RETURN
		 IF((CALCULATE(SUM('Staff Booking'[Loading]), FILTER('Staff Booking', 'Staff Booking'[Name ] = staffname),FILTER('Staff Booking', 'Staff Booking'[Booking Type ] == "Chargeable"), FILTER('Staff Booking', 'Staff Booking'[Start Date].[Date] <= jobend &&  jobstart <= 'Staff Booking'[End Date].[Date] )))  > 100 && bookingtype = "Chargeable" , "Overbooked", "Normal")

This works well for most cases, however there is one case where it goes wrong:

The dark blue means the job is overloaded(>100) according to the dax formula above, and the light blue means job is Normal (100).

However the dark blue(first and second) here are not correct, because they never get more than 100 at any period of time. The reason why they are marked as overloaded is because the dax formula is combining the loading for all the different time period that overlaps.

Basically if the dax formula above can be tweaked to seggregate base on time period

e.g. Job 12 = Job 12 + Job 10 + Job 1 + Job 20 = 120 

which is wrong because Job 12 is always 100 at any time period

e.g.  Job 12 = Job 12 + Job 10 + Job 1 = 100 and Job 12 = Job 12 + Job 10 + Job 20 = 100

[Anonymous]

Employee Booking Type  Jobs WorkLoad% Start Date  End date
John Chargeable CNS 20 04/11/2019 31/01/2020
John Chargeable CNS 20 04/05/2020 27/11/2020
John Chargeable Integrated LLC 100 01/10/2019 31/12/2019
John Chargeable BCS 100 14/10/2019 01/11/2019
John Vacation/Holiday SN 100 30/09/2019 11/10/2019
John Vacation/Holiday Annual leave 100 23/12/2019 24/12/2019
John Chargeable Tech PLC 50 29/07/2019 30/09/2019
John Chargeable Integrated LLC 50 29/07/2019 30/09/2019
John Chargeable Accenture Pte Ltd  80 16/03/2020 15/05/2020
John Vacation/Holiday SN 100 10/01/2020 10/01/2020
John Chargeable CITY 100 23/03/2020 30/07/2020
John Chargeable SiH 50 20/01/2020 20/03/2020
John Chargeable CNS 20 03/02/2020 20/03/2020
John Chargeable CDLHT 30 22/01/2020 20/03/2020
Bernard Chargeable BCA 100 01/07/2019 30/09/2019
Bernard Chargeable Ecquaria 100 07/10/2019 28/02/2020
Bernard Vacation/Holiday Annual leave 100 22/01/2020 30/01/2020
Bernard Vacation/Holiday Annual leave 100 13/08/2019 16/08/2019
Bernard Chargeable SiH 100 01/10/2019 04/10/2019
Bernard Chargeable PSD 100 01/10/2019 04/10/2019
Bernard Vacation/Holiday Family Care 100 05/05/2020 06/05/2020
Bernard Chargeable UOI 100 02/03/2020 27/03/2020
Bernard Chargeable Ecq 100 30/03/2020 30/07/2020
Bernard Chargeable Sun 100 02/03/2020 27/03/2020
Bernard Chargeable Sun 50 30/03/2020 10/04/2020

Employees have a list of jobs for various time periods.

To track employee's efficiency for any time period we have a Loading system.

Each job has a certain Loading percentage. Employee can be put on multiple jobs, however the combined Chargeable loading cannot exceed 100. 

If the jobs are plotted on a gantt chart

Picture above shows only the chargeable jobs, Top-left job's loading is 50, Middle job's loading is 20, Bottom job's loading is 30.

Total is 100 which is fine

However if Top-right job's loading is 80, Middle job's loading is 20, Bottom job's loading is 30.

Total loading for the Top-right and bottom two job's is 130, which is overloaded for that job.

I want to go through each job and calculate if it ever overlaps other Chargeable jobs and what is the total loading for that job + other overlapped jobs.

Then in a new column:

Overloaded (for Chargeable  loading > 150)

Underloaded (for Chargeable  loading < 100)
Normal (Chargeable loading = 100 or the row contains non-Chargeable  jobs)

[dax]

Hi @Anonymous ， 

I am not clear about your requirement, could you please explain the logic of "loading status" and if possible, could you please inform me your current design in gantt chart and your expected output? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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