Forum Discussion
Microsoft Project Resource Allocation by Month
I have Power BI connected to Microsoft Project through PWA and for the projects I am managing I am trying to see how much work a resource has in a given month. Project tasks only have a start date and an end date and pulling one of those won't show the full picture for any month in between those two months. Below is what I am looking for:
Data Example:
| Task | Resource | % Allocated | Start | End | |||
| Task A | Resource 1 | 25% | 1/1/2023 | 4/30/2023 | |||
| Task B | Resource 1 | 75% | 3/1/2023 | 8/30/2023 | |||
| Task C | Resource 2 | 10% | 1/1/2023 | 6/30/2023 | |||
| Task D | Resource 2 | 90% | 4/1/2023 | 12/31/2023 |
Desired Result:
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | ||
| Resource 1 Total | 25% | 25% | 100% | 100% | 75% | 75% | 75% | 75% | 0% | 0% | 0% | 0% | |
| Resource 2 Total | 10% | 10% | 10% | 100% | 100% | 100% | 90% | 90% | 90% | 90% | 90% | 90% |
Thanks!
This solution uses a disconnected date table DimDateVisual (no relationships). This name will allow it to coexist with your main date table if you have one.
Create measure:
Resource Allocation = VAR vMinDate = MIN ( DimDateVisual[Date] ) VAR vMaxDate = MAX ( DimDateVisual[Date] ) VAR vTable = FILTER ( FactTable, FactTable[Start] <= vMaxDate && FactTable[End] >= vMinDate ) VAR vAmount = CALCULATE ( SUM ( FactTable[% Allocated] ), vTable ) VAR vResult = IF ( ISBLANK ( vAmount ), 0, vAmount ) RETURN vResultThe visual uses the Month column in DimDateVisual:
2 Replies
This solution uses a disconnected date table DimDateVisual (no relationships). This name will allow it to coexist with your main date table if you have one.
Create measure:
Resource Allocation = VAR vMinDate = MIN ( DimDateVisual[Date] ) VAR vMaxDate = MAX ( DimDateVisual[Date] ) VAR vTable = FILTER ( FactTable, FactTable[Start] <= vMaxDate && FactTable[End] >= vMinDate ) VAR vAmount = CALCULATE ( SUM ( FactTable[% Allocated] ), vTable ) VAR vResult = IF ( ISBLANK ( vAmount ), 0, vAmount ) RETURN vResultThe visual uses the Month column in DimDateVisual:
I got it! Thank you!
