Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |