Forum Discussion

Prath's avatar
Prath
Frequent Visitor
5 years ago
Solved

Compile monthly resource capacity

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?

 

 

Data Protection
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

2 Replies

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.

  • Prath's avatar
    Prath
    Frequent Visitor
    5 years ago

    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