Forum Discussion
Power BI - Time Intelligence "State-in-time" Chart
Hello! I am looking to create a graph that shows the status of different items (rows of data) based on the dates of different activities that are included in the columns.
So, I would want on the x axis to be the month and on the y axis would be the count of instances. And it would be a stacked bar chart showing the count of items in each "Initial Work," "Pending," "Approved," and "Complete" on a given date. So for 1/1/19 I would see just a count of 3 for initial work. For 4/1/19 you would see 1 initial work, 3 pending, 5 approved, 3 complete in a stacked bar chart.
I am really just looking for some direction on the best way to tackle this problem. I am assuming I will need to have measures to count on a specific date saying if complete and approved are blank, then count, etc..., but I am not sure the best way to set it up (or if it is even possible??). Thank you in advance!!
Also - wanted to add that I can see how you could do this in excel easily with a few countif functions and create a table based on dates and use the second table for the chart... but this is a part of a larger project where we are setting up a dashboard, so if there is a way to keep it in BI that would be preferred.
Yes, I was able to find a solution! Basically I did the following:
- Create date master table that has all the relevant dates included.
- Create “Status Changes” table
- This is an unpivoted version of the source table - gives the “start date” for each status.
- Added status sort order column and use this to sort the table for non-alphabetical statuses.
- Calculate the Max status for each Name
- Calculate the last day in state based on the start date in the next state (or if it is the max status, then there is no last date)
- On the Date Master, create formulas to count the number of items in a certain status based on the date beginning and ending range.
Let me know if that helps or if you need any clarification!
6 Replies
You will have to unpivot the columns and convert them to row. e..g https://www.newtechdojo.com/unpivot-columns-in-power-bi/
Then use stacked column chart: https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-combo-chart
v-frfei-msftCommunity Support
Hi swiley9 ,
Select the Name column and unpivot Others columns as below.
M code for your reference.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjC0RGcaI5gmEGasTrSSE1jAiJAOU4QOZ3RlRhgG6yiBVboQoxLFNa7oyoyxakZyjRu6HHamGUKHO6rBeFR6oMrhUemJxUwTrCq90MIJgkAy3ugeRjYOrMIHZ+jAVPjiiGmECj8coY8jTmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"Initaial Work" = _t, Pending = _t, Approved = _t, Complete = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Initaial Work", type date}, {"Pending", type date}, {"Approved", type date}, {"Complete", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value") in #"Unpivoted Other Columns"Then we can create starcked column chart after applying the steps in power query.
For more details, please check the pbix as attached.
