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.
Hi everyone!
I am new to Power BI and still discovering how to make insightful visualizations in it! I am managing a few projects and am struggling to make a time series type bar chart based on data that has static values. This is a very simplified example of the data i am working with (stages are incremental and if there is no entry date for next stage, that means that the project is still in its previous stage - project A continues to be in stage 2 from feb to current month):
Project name | Stage 1 entry date | Stage 2 entry date | Stage 3 entry date |
A | 1/1/2021 | 2/1/2021 | |
B | 2/1/2021 | 3/1/2021 | 5/1/2021 |
C | 2/1/2021 | 3/1/2021 | 6/1/2021 |
I want to create a bar chart that will show me how many projects are in each stage in each month. Some thing like this:
Can you help me on how I can get this? Thank You!!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
What is the maximum number of stages you can have?
There are 6 stages in total and the stages don't change.
Hi,
You may download my PBI file from here.
Hope this helps.
That looks like it will definitely solve my problem! Although being the rookie I am, I don't know how to use the zipped file you've shared... how do I open the shared file?
It is not a zipped file. It is a PBI file.
When i try to download the file, it comes up as pbix.zip and when i double click, it shows up as this: not sure if i am doing something wrong here..sorry!
Hi,
I do not know why you see that. This is what i see when i click on the link
Thanks! I think because I was downloading your file in a mac, it was by default zipping it to download. When i tried to download your file on windows, it worked! Thanks a lot for sharing it 🙂
You are welcome.
Hi Ashish, I tried your solution and it gets me close to what I need, but there is a small hickup. My data looks like this:
Project name | Stage 1 entry date | Stage 2 entry date | Stage 3 entry date |
A | 1/1/2021 | 2/1/2021 | |
B | 2/1/2021 | 3/1/2021 | 5/1/2021 |
C | 2/1/2021 | 3/1/2021 | 6/1/2021 |
when I pivot the table to get it in this format as mentioned in your solution, since I only have stage entry dates and not dates when a particular project stayed in its stage: e.g. the above table doesn't explicitly tell that project C was in stage 2 from 3/1/2021 to 6/1/2021. I thus don't have the highlighted rows shown below. It seems like I will have to fill rows for each and every project that corresponds to every month between stage entry dates, in order for the projects to be counted in months in which they did not move to other stages:
Can you help me understand how I can achieve this? I hope I am clear in my explanation above!
Hi,
That is exactly the transformation i have been able to achieve in the Query Editor. Please go to the Query Editor and study the steps there.
Since you want this by month you need a separate (disconnected) table with month names and codes. What should happen at the end of the year, by the way?
Then you create a measure that calculates for each stage how many projects were active in that month.
My idea was that the visualization keeps automatically updating to the current month. Projects get added on, shifted to other stages every so often and the idea is to get a historical progression of projects within stages till today/current month. About your comment on connecting month table to the table I have shown above, I am not sure how to connect the two tables with a common variable. I was thinking that the logic would be "go through each project entry in table 1, if stage x entry date is for e.g. Jan 2020, then no. of projects in Jan 2020 in stage x becomes +1. But I am not sure how to connect the month and project table to do that.
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |