One important function of a resource manager or a project manager is to monitor the assignments for each of their resources so that they can effectively balance their team’s workloads while minimizing overallocation. To efficiently manage the resources and make necessary adjustments, project managers or resource managers need to be able to view their workloads and availability.
The Power BI Report Template for Project for the web provides a solution for powerful and effective resource management. A “Resource Allocation” report page has been added to the Power BI Template. This report can help answer questions such as how much work is allotted to a resource each day, what are the active tasks a resource needs to work on, or what are the future tasks allotted to a resource.
Where can you find the resource allocation report?
You can find the updated report here: aka.ms/ProjectReports
You can also build an allocation report on top of your previously downloaded and customized report pack by following the steps below.
How to build the allocation report?
To build a resource allocation report, you will need resource timephased data. The time phased data is stored in the Resource Assignment table . Follow the steps to get work allocated to a resource for each day:
1. Open your Project for the web Power BI Template in Power BI desktop app
2. Create a new blank query and use the “Resource Assignment Staging” table as the source
3. Remove the columns that are not needed but keep msdyn_bookableresourceid, msdyn_plannedwork, msdyn_projectid, msdyn_resourceassignmentid, msdyn_taskid.
4. Filter out any msdyn_plannedwork rows for non-null values.
5. Transform msdyn_plannedwork column to JSON.
6. Expand the List data in msdyn_plannedwork to new rows and then expand data in the records (End, Hours and Start).
7. Remove the “/Date(“ from the End and Start columns using the Replace Values function.
8. The end and start are milliseconds from midnight 1970/01/01. To convert them into the required date format, add a new custom column to calculate the End and Start date from the milliseconds value.
Planned Work End= #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[PlannedWork.End]/1000)
9. Change the type of new columns to Date.
That’s it, now you can build any visualization on top of this data that you prefer.
Please submit feedback through in-app feedback button on new scenarios you would like in your Power BI Template for Project for the web.
