cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shreyaspuranik
Frequent Visitor

Creating time series visualization from data snapshot

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 nameStage 1 entry dateStage 2 entry dateStage 3 entry date
A1/1/20212/1/2021 
B2/1/20213/1/20215/1/2021
C2/1/20213/1/20216/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:

 

shreyaspuranik_0-1631289884383.png

 

Can you help me on how I can get this? Thank You!!

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

Hi,

What is the maximum number of stages you can have?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

There are 6 stages in total and the stages don't change. 

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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! 

shreyaspuranik_0-1631505455215.png

 

Hi,

I do not know why you see that.  This is what i see when i click on the link

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 nameStage 1 entry dateStage 2 entry dateStage 3 entry date
A1/1/20212/1/2021 
B2/1/20213/1/20215/1/2021
C2/1/20213/1/20216/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:

 

shreyaspuranik_0-1631547907617.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.