Hi all,
I have a question about pivot and results for an overview.
Attached there is an excel file, with an table of tasks from A1:H25.
There are different dates enteres, which describes, which task passed into next Phase. So an regular result is doable, like my both pivots in K2:V4 and K8:U10. So I can figure out, how many tasks where entered, and passed into a next phase.
My challange is no, to figure out, how many tasks where open at the end of an month. From K32:W37 I´ve tried to explain this:
- Task_05 passed Phase 1 in January and turned into Phase 2. As this was finalized in March, this task was in end of January and Februrary open. (orange highlighted)
- Task_02 and Task_14 passed Phase 1 in March and turned into Phase 2. As one was finalized in April and one in May, there where 2 tasks open at the end of March, and one at the end of April. (green highlighted)
- In september I get 7 results, as they passed Phase 1 before end of September and Phase 2 after. (light red highlighted)
- As the blue ones passed in same month, they should ignored.
Is there any chance, to make this visible in one Pivot for the different Phases?
Unfortunately, I don't think it's possible.
To show a task as open in months 1 and 2 and closed from month 3 onward, you need to have a status column at the end of each month.
Based on your initial table, you have to write a custom vba code that populates another table, properly structured for the desired output.
Initial table must be converted to a tabular structure, like:
| Tasks | Phase | Date |
| Task_01 | 0 | 12/03/2021 |
| Task_01 | 1 | 12/03/2021 |
| Task_01 | 2 | 31/03/2021 |
| Task_01 | 6 | 12/04/2021 |
Hello Catalin,
many thanks for your answer 🙂
Unfortunately I have no experience with VBA.
Will it possible, to get these structure also with Power Query?
Here I´m also not an expert, but I´m able to understand this much better than vba 🙂
Hello Catalin again,
🙂
I think I got it now on Power Query.
On line F46 of the attached file it works 🙂
Unfortunately I have now all dates in my Pivot on K46.
"Task 01" passed in 03/2021 from Phase0 up to Phase2. In this case it jumed immediately to Phase6 and we don´t have a dates from Phases between.
So this task remains already end of March in Phase 6 and was finalized in April.
So I have now threee entries in March, and one in April, but I need just one for "remaining in Phase 6" in April.
Did I something wrong?
Instead of removing null dates, sort tasks and phases properly and Fill down the date column, for Task1 as an example it will populate Phases 3-5 with phase 2 date. Is this what you wanted?
Quelle = Table.Combine({Phase0, Phase1, Phase2, Phase3, Phase4, Phase5, Phase6}),
#"Sorted Rows" = Table.Sort(Quelle,{{"Tasks", Order.Ascending}, {"Phase", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Date"})
in
#"Filled Down"
