Summarise resource allocation by time periods

[gianfrancob]

Hello everyone, 

I am new on Power Bi and I was trying to implement a function that I would normally create in excel quite easily. 

All I want to do is pretty simple, given the below dataset:

Resource	Task	Start	End	allocation
Resource A	Task A	01/01/2018	15/01/2018	50
Resource A	Task B	01/01/2018	08/01/2018	50
Resource B	Task C	01/01/2018	08/01/2018	100
Resource C	Task C	01/01/2018	22/01/2018	50
Resource C	Task E	08/01/2018	29/01/2018	20

I would like to generate an aggregate table/pivot similar to a Gantt chart which would give me:

for each resource its allocation in a particular time period, like this:

Allocation	01/01/2018	08/01/2018	15/01/2018	22/01/2018	29/01/2018
Resource A	100	100	50	50	50
Resource B	50	0	0	0	0
Resource C	50	70	70	20	0

I believe that should be a fairly common pattern. I found a lot of examples when the original table has only one date but couldn't find anything covering a period (ed with start and end date).

Any suggestion on how to do the above in power bi?

Thank you!

[GustavoVianna]

Hi @gianfrancob , did you manage to get this done?  I was hoping to find a solution for this one.

[v-piga-msft]

Hi @gianfrancob,

By my test with your data sample, I could get the output below which has a little different from your expected output.

However, I have a little confused about the value in your desired output, if is convenience, please describe your logic in more details.

If you want to get the output I upload above. You could follow the steps in Query Editor.

Best Regards,

Cherry

[gianfrancob]

Hi @v-piga-msft,

thanks for your solution. It doesn't quite do what I am looking for. I will try to be more specific:

I have a table, let's call it ALLOCATION, which contains

Resource

Assignment

Allocation

Allocation Start Date

Allocation End Date.

As you can see below, resources can have more than 1 assignment and the period between assignments could overlap (or not)

Resource	Task	Start	End	allocation
Resource A	Task A	01/01/2018	15/01/2018	50
Resource A	Task B	01/01/2018	08/01/2018	50
Resource B	Task C	01/01/2018	08/01/2018	100
Resource C	Task D	01/01/2018	22/01/2018	50
Resource C	Task E	08/01/2018	29/01/2018	20

What I would like to achieve is a sort of "timeline" where for a given time period (it could be a day, a week or a month). In summary, I would need to be able to select time buckets (DAYS, WEEKS mainly), which I understand in Power BI can be done using a DATE table.

Given the time period, I would like to calculate the SUM of the allocation for that particular resource in that particular time period. 

That is, let's assume we take Time period= DAY (columns), the results in the rows should be the below

ALOOCATION	01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan
Resource A	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	100 (TASK A + B )	50 (TASK A)	50 (TASK A)	50 (TASK A)
Resource B	100 (TASK C )	100 (TASK C )	100 (TASK C )	100 (TASK C )	100 (TASK C )	100 (TASK C )	100 (TASK C )	100 (TASK C )	0	0	0
Resource C	50 (TASK D)	50 (TASK D)	50 (TASK D)	50 (TASK D)	50 (TASK D)	50 (TASK D)	50 (TASK D)	70 (TASK D + E)	70 (TASK D + E)	70 (TASK D + E)	70 (TASK D + E)

In Excel, I normally implement this with SUMIF. I sum the allocation ONLY if the DAY is in the period the allocation is ACTIVE.

Hope my explanation is clear now. 

ANy help would be much appreciated!

Thank you.