Create Table for Resource Tracking

[Tektolnes]

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!

[v-lili6-msft]

hi  @Tektolnes 

For your case, you could try this simple way:

First, you should have a date table.

Second, then you could create a new calculated table

New table = FILTER(CROSSJOIN('Table','Date'),'Date'[Date]>='Table'[Start]&&'Date'[Date]<='Table'[End])

Result:

and here is my sample pbix file, please try it

Regards,

Lin

[amitchandak]

Refer to my blog and check how current employees has been generated. The same way you can get this

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -Decoding Direct Query - Time Intelligence , Winner Coloring on MAP , >HR Analytics , Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin