Power BI - Time Intelligence "State-in-time" Chart

[swiley9]

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. 

[v-frfei-msft]

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.

[swiley9]

Thanks! The problem is that both of these options only show me the date that things change, and not the status on a given date! Still looking for a solution to this, didn't realize it would be such a complicated question!

[yoav_lubo]

@swiley9 

Were you able to find a solution?

[swiley9 · Accepted solution]

Yes, I was able to find a solution! Basically I did the following: 

1. Create date master table that has all the relevant dates included.
2. Create “Status Changes” table
1. This is an unpivoted version of the source table - gives the “start date” for each status.
2. Added status sort order column and use this to sort the table for non-alphabetical statuses.
3. Calculate the Max status for each Name
4. 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)
3. 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!

[yoav_lubo]

Thank you very much, kudos to you!

Any chance of sharing a PBIX?

[Anonymous]

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