Resource Allocation Model with Multiple Gates and Durations

[MDS20]

Intent is to visualize, by month, the total percent allocated for each project lead across all their projects. I'm struggling with how to take the data available to me and make it usable with a stacked bar chart. I've considered slicing the data into a table for each "D phase" and then appending them but that seems cumbersome given the cabilities within PowerBi

I've made a simplified bar chart in Excel for reference of the base level I'd be looking to achieve with the given data. 

Sample data set below.

project name

Lead

lead % D0

D0 Start

D0 End

lead % D1

D1 Start

D1 End

lead % D2

D2 Start

D2 End

lead % D3

D3 Start

D3 End

lead % D4

D4 Start

D4 End

lead % D5

D5 Start

D5 End

ABC

Carl

2%

1/5/2025

2/2/2025

5%

2/2/2025

2/27/2025

10%

2/27/2025

4/8/2025

10%

4/8/2025

7/7/2025

25%

7/7/2025

10/10/2025

2%

10/10/2025

10/10/2026

EFG

Emily

1%

1/6/2025

3/1/2025

7%

3/1/2025

3/26/2025

8%

3/26/2025

5/5/2025

10%

5/5/2025

8/3/2025

20%

8/3/2025

11/6/2025

5%

11/6/2025

11/6/2026

HIJ

Zach

2%

12/5/2024

2/1/2025

1%

2/1/2025

2/26/2025

10%

2/26/2025

4/7/2025

9%

4/7/2025

7/6/2025

15%

7/6/2025

10/9/2025

2%

10/9/2025

10/9/2026

KLM

Carl

2%

2/6/2025

2/25/2025

5%

2/25/2025

3/22/2025

5%

3/22/2025

5/1/2025

8%

5/1/2025

7/30/2025

25%

7/30/2025

11/2/2025

5%

11/2/2025

11/2/2026

NOP

Zach

3%

1/6/2025

2/10/2025

2%

2/10/2025

3/7/2025

7%

3/7/2025

4/16/2025

10%

4/16/2025

7/15/2025

30%

7/15/2025

10/18/2025

5%

10/18/2025

10/18/2026

QRS

Emily

3%

2/16/2025

3/25/2025

1%

3/25/2025

4/19/2025

6%

4/19/2025

5/29/2025

10%

5/29/2025

8/27/2025

40%

8/27/2025

11/30/2025

5%

11/30/2025

11/30/2026

TUV

Megan

5%

11/5/2024

1/2/2025

2%

1/2/2025

1/27/2025

15%

1/27/2025

3/8/2025

25%

3/8/2025

6/6/2025

30%

6/6/2025

9/9/2025

5%

9/9/2025

9/9/2026

[MDS20]

@v-pgoloju , the solution works given the sample data provided, thanks! I have since had to pivot from the source data and structure so I've had to take a different approach.

[v-pgoloju · Accepted solution]

Hi @MDS20,

We wanted to kindly check in to see if everything is working as expected after trying the suggested solution. If there’s anything else we can assist with, please don’t hesitate to ask.

If the issue is resolved, we’d appreciate it if you could mark the helpful reply as Accepted Solution  it helps others who might face a similar issue.

Warm regards,

Prasanna Kumar

[v-pgoloju]

Hi @MDS20,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

If the response addressed your query, kindly mark it as Accepted Solution and click Yes if you found it helpful — this will benefit others in the community as well.

Best regards,

Prasanna Kumar

[v-pgoloju]

Hi @MDS20,

Thanks for reaching out to the Microsoft Fabric Forum Community.

To create a stacked bar chart in Power BI showing each lead’s percent allocation per month, you’ll need to Transform data. Here's how you can approach it.

1. Unpivot D0 to D5 columns in Power Query, so each phase becomes a row instead of a set of columns. This makes the data easier to work with.

2. Since each phase has a start and end date, you’ll want to expand these rows to include each individual day the lead is working on the project.

3. Create a date/calendar table in your model with columns like Date, Month, and Year. You’ll need this to summarize data by month.

4. Make sure the daily allocation table is connected to the calendar table through the date column. You can also connect it to a Lead table if you want better filtering.

If this helped, please mark the response as the accepted solution and give it a thumbs-up so others can benefit too.

Best regards,
Prasanna Kumar