Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
vResult
The visual uses the Month column in DimDateVisual:
Proud to be a Super User!
I got it! Thank you!
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
vResult
The visual uses the Month column in DimDateVisual:
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |