Forum Discussion
Burn-up graph based on multiple variables
Hi,
I am experiencing some issues with a burn-up graph I am experimenting with. I am having data on the following form:
| Case key | Start Date | Case Type | Case Status |
| A-1 | 11-05-2019 | A | Open |
| A-2 | 11-05-2019 | A | In progress |
| B-1 | 11-05-2019 | B | Open |
| B-2 | 11-05-2019 | B | Closed |
| C-1 | 12-05-2019 | C | Open |
| C-2 | 12-05-2019 | C | In progress |
| A-1 | 12-05-2019 | A | In progress |
| B-3 | 13-05-2019 | B | Closed |
| A-1 | 13-05-2019 | A | Closed |
Note that a case can have any status from the start. Based on the dates and the 'case status' variable I need to create a burn-up chart in PBI displaying the dates on the x-axis and the number of cases having the different statusses on the y-axis. Something like this. Notice that once the case status changes it moves to another status in the graph, and its previous status should no longer be represented by subsequent dates.
To be able to do so, I believe I have to first calculate the total case type for each day as a measure
TotalCaseType = COUNT('Table'[Case Status])
and then use a PBI table to divide the counts into case type, date and case status. This should be doable in a stacked area chart as well? This works decently and I obtain a table like this:
| Status Start Date | Issue Type | Status | Count | Count Running Total |
| 11-05-2019 | A | Open | 1 | 1 |
| 11-05-2019 | A | In progress | 1 | 1 |
| 11-05-2019 | B | Open | 1 | 1 |
| 11-05-2019 | B | Closed | 1 | 1 |
| 12-05-2019 | C | Open | 1 | 1 |
| 12-05-2019 | C | In progress | 1 | 1 |
| 12-05-2019 | B | Open | 1 | 1 |
| 12-05-2019 | A | In progress | 1 | 2 |
| 13-05-2019 | A | Closed | 1 | 1 |
| 13-05-2019 | B | Closed | 1 | 2 |
Now I would like to create a running total over the dates that divides the cumulation based on both Case Type and Case Status (the variable displayed to the far right). Futhermore, as mentioned avobe, once the case status changes, its previous status should no longer be represented on subsequent dates (in the running total). Note for instance how A-1 is counted in the running total of 'In progress' once it changes stauts the first time, and only in the running total of 'closed' when it changes status the second time. To create such a measure, I have tried various things, such as
CALCULATE(
COUNT('Table'[Case Status]);
FILTER(
ALL('DateTable');
'DateTable'[FullDateAlternateKey] <= MAX('DateTable'[FullDateAlternateKey])))but everything seems to just return the count for the specific categories on a given date, and not a running total. What am I doing wrong?
You can create a new table in Power Query, with the below script.
You can start with the source, and then add copy the other steps in the Advanced Editor.
This also assumes that you have a calendar table called "Date" (bold and large).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1VNJRMtU3NNQ3MjC0BLIdgdi/IDVPKVYHJG2ERdozT6GgKD+9KLW4GKzKCcMQJ2RDnDAMAUk75+QXp6aAFTjD9BvBFDgj63eG6UeRRneEI4Yh2J1qDFFljMMtcGOMkY2BKYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case key" = _t, #"Start Date" = _t, #"Case Type" = _t, #"Case Status" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Case key"}, {{"Min Date", each List.Min([Start Date]), type date}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max Date", each List.Max(Table.Column(#"Grouped Rows", "Min Date"))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Date"}, {"Custom.Date"}), #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "In Range", each if [Min Date] <= [Custom.Date] and [Max Date] >= [Custom.Date] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([In Range] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Min Date", "Max Date", "In Range"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Case key", "Custom.Date"}, Status, {"Case key", "Start Date"}, "Status", JoinKind.LeftOuter), #"Expanded Status" = Table.ExpandTableColumn(#"Merged Queries", "Status", {"Case Type", "Case Status"}, {"Status.Case Type", "Status.Case Status"}), #"Sorted Rows" = Table.Sort(#"Expanded Status",{{"Case key", Order.Ascending}, {"Custom.Date", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Status.Case Type", "Status.Case Status"}) in #"Filled Down"You can download the pbix here.
Hope this helps,
Nathan
2 Replies
You can create a new table in Power Query, with the below script.
You can start with the source, and then add copy the other steps in the Advanced Editor.
This also assumes that you have a calendar table called "Date" (bold and large).
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1VNJRMtU3NNQ3MjC0BLIdgdi/IDVPKVYHJG2ERdozT6GgKD+9KLW4GKzKCcMQJ2RDnDAMAUk75+QXp6aAFTjD9BvBFDgj63eG6UeRRneEI4Yh2J1qDFFljMMtcGOMkY2BKYgFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case key" = _t, #"Start Date" = _t, #"Case Type" = _t, #"Case Status" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Case key"}, {{"Min Date", each List.Min([Start Date]), type date}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Max Date", each List.Max(Table.Column(#"Grouped Rows", "Min Date"))), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each Date), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Date"}, {"Custom.Date"}), #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "In Range", each if [Min Date] <= [Custom.Date] and [Max Date] >= [Custom.Date] then 1 else 0), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([In Range] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Min Date", "Max Date", "In Range"}), #"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Case key", "Custom.Date"}, Status, {"Case key", "Start Date"}, "Status", JoinKind.LeftOuter), #"Expanded Status" = Table.ExpandTableColumn(#"Merged Queries", "Status", {"Case Type", "Case Status"}, {"Status.Case Type", "Status.Case Status"}), #"Sorted Rows" = Table.Sort(#"Expanded Status",{{"Case key", Order.Ascending}, {"Custom.Date", Order.Ascending}}), #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Status.Case Type", "Status.Case Status"}) in #"Filled Down"You can download the pbix here.
Hope this helps,
Nathan
Anonymous
Works like a charm, thanks - This is a great help! Now I have only a small problem.
In my dataset, a case can change status multiple times during a single day and can therefore have multiple statusses in the span of a day. Assuming I have a time variable as well, how do i ensure that only the 'newest' case status is displayed in my graph?
Best regards and thank you in advance.
