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 assign...
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.

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