Microsoft Project Resource Allocation by Month

[bcummings12345]

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!

[bcummings12345]

I got it! Thank you!

[DataInsights · Accepted solution]

@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: