Forum Discussion

bcummings12345's avatar
bcummings12345
Frequent Visitor
3 years ago
Solved

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 an...
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    bcummings12345,

     

    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:

     

     

DataInsights's avatar
DataInsights
Super User
3 years ago

bcummings12345,

 

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: