Project Task resource allocation table

[Vin57]

Hello,

I am trying to create a Task allocation heatmap from all the projects we have and our input data is in below first table format,

Task	Assigned To	Start Date	End Date	Duration (Days)	Allocation (ratio)
Task 1	Person 1	2/23/2024	2/25/2024	3	0.5
Task 2	Person 2	2/25/2024	3/1/2024	6	0.1
Task 3	Person 3	2/27/2024	3/4/2024	7	1

(this table shows the allocation of each day from the start to end)

and looking for a output table like shown below.

Assigned To	2/23/2024	2/24/2024	2/25/2024	2/26/2024	2/27/2024	2/28/2024	2/29/2024	3/1/2024	3/2/2024	3/3/2024	3/4/2024
Person 1	0.5	0.5	0.5
Person 2			0.1	0.1	0.1	0.1	0.1	0.1
Person 3					1.0	1.0	1.0	1.0	1.0	1.0	1.0
Total Resources	0.5	0.5	0.6	0.1	1.1	1.1	1.1	1.1	1	1	1

(this table expands the first table and lets me calculate the total resources planned for each day)

It will be really great if i can add a slicer to this for days, weeks, months and year too.

I did search through similar posts here and doesnt depcit any solution like the requirements I have.

I will be really greatful if someone can help me with pbix file with solution 🙂

Thanks in Advance!

[lbendlin · Accepted solution]

This needs a little more work.  You need a calendar dimension and a resource dimension.  Then you can do a cross join and a measure that ties it all together.  Feel free to add months and years etc to the calendar table.

[Vin57]

Hi @lbendlin , Just now realized that I missed one logic here. How can I make the allocation not to calculate when there's Saturday and Sundays? Is there a way in the date table that I can mark ignore the days (Weekends and Holidays. For Holidays, I can update them manually) so that the allocation doesnt fall in those days.

(I am getting all the date input from smartsheet and it auto adds Weekends when I enter the start date and duartion. So the data input is good and because of we use the allocation to full from start to end dates we are adding additional resources)

Thank you very much for your time!

[lbendlin]

In line 3 you can add a WEEKDAY() filter against the [Date]  column.

[Vin57]

Thank you very much. This works like charm! 😊