Visualise workload by consultant

Hi v-huizhn-msft,


Thank you for the response. My source is actually a SharePoint list where users enter the Start Date, End Date, Hours, the Excel screenshot was just to show the end result I want to achieve in the Power BI report.

The Start Date determines which Calendar week the Hours get allocated to and summed up for.

Example:

Job 1
Consultant: p1
Start Date:  1 Aug 2018
Hours:    12

Job 2
Consultant: p1
Start Date:  3 Aug 2018
Hours:    6
 
Job 3
Consultant: p1
Start Date:  11 Aug 2018
Hours:    8

Job 3
Consultant: p2
Start Date:  11 Aug 2018
Hours:    3


The above then gets summarized by Consultant by week as follows:

Consultant:            p1
August week 1 Hours:   18 (12 +6)


Consultant:            p1
August week 2 Hours:   8

-----------------------------------------

Consultant:            p2
August week 1 Hours:   0


Consultant:            p2
August week 2 Hours:   3



Once I have the measure summing the total hours per week per consultant I can then put that in a Power BI table to see week by week how busy Consultants are, i.e. are they over 100% allocated then we need more resources to do the job.


Attached picture is my attempt at deriving that measure with the given data I have, but obviously that formula is not right so far :(


Any ideas how I might get the desired result using a measure please?

Thanks,
Nick