Forum Discussion
Create Table for Resource Tracking
I'm trying to create a resource tracking table which would get populated from a single source where projects details are stored. For example the project details repository would have something like the below:
| Project ID | Resource | Start | End |
| REF-1 | 1 | 01/01/2020 | 17/01/2020 |
| REF-2 | 0.5 | 07/01/2020 | 13/01/2020 |
| REF-3 | 1.5 | 03/01/2020 | 09/01/2020 |
What I would like to do is have Power BI create a table which showed all the dates in a year, the projects and the number of resources assigned to them. Maybe something like this:
| Date | REF-1 | REF-2 | REF-3 |
| 01/01/2020 | 1 | 0 | 0 |
| 02/01/2020 | 1 | 0 | 0 |
| 03/01/2020 | 1 | 0 | 1.5 |
| 06/01/2020 | 1 | 0 | 1.5 |
| 07/01/2020 | 1 | 0.5 | 1.5 |
| 08/01/2020 | 1 | 0.5 | 1.5 |
| 09/01/2020 | 1 | 0.5 | 1.5 |
| 10/01/2020 | 1 | 0.5 | 0 |
| 13/01/2020 | 1 | 0.5 | 0 |
| 14/01/2020 | 1 | 0 | 0 |
| 15/01/2020 | 1 | 0 | 0 |
| 16/01/2020 | 1 | 0 | 0 |
| 17/01/2020 | 1 | 0 | 0 |
| 20/01/2020 | 0 | 0 | 0 |
| 21/01/2020 | 0 | 0 | 0 |
With summarization, known total available resources, etc. I could then produce visualizations which would show resource deficits, when resources might be freed up, etc.
I have an idea of how I could do it in Excel but I'd prefer to not have to maintain something in Excel first and have Power BI work it out automatically if possible based on the first table provided above. Is this possible or what would be the best practice for handling this?
Thanks!