Compile monthly resource capacity

[Prath]

I am trying to create a resource capacity matrix and need help with creating a measure that would aggregate the capacity of a resource by each month based start date & end date of the projects assigned to the resource.

Attached the pbix with data gdrive_link 

Here is how the data would look like

Is there a way to create a measure that would sum the capacity for each month so I can build a matrix like this for all months?

[Prath]

Thanks @v-robertq-msft , that helped.

One question though. How would I tweak it so that the calculation would include the rows that don't necessarily have a start or end date as 1st? Was hoping to include the month as long as the date is in that specific month.

For example, in this case:

1st row would include both Jan & Feb in the calculation

2nd row would include both April & May in the calculation

[v-robertq-msft · Accepted solution]

Hi, @Prath 

According to your description, I can roughly understand your requirement, I think you can try my measure to achieve this:

First, you should delete the relationship between the calendar table with the main table to avoid errors.

Then create a measure like this:

Capacity1 =

var _value=

CALCULATE(SUM(Data[Capacity]),FILTER('Data',[Start]<=MIN('Calendar_Date'[Date])&&[End]>=MAX('Calendar_Date'[Date])))

return

IF(_value=BLANK(),0,_value)

Then create a matrix and place it and apply a filter like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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