Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
Hi @gianfrancob , did you manage to get this done? I was hoping to find a solution for this one.
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
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.
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |