Help with counters by day and by status on weekly chart

[justuser123]

Hi everyone!

I have a little issue and hope to get a good advice.

For a simplicity, let say I have a model with the three tables.

1. Incident table “Incidents” with a unique Incident ID (UID) field.
2. Status change table “Statuses” with the fields: Incident ID (UID); Start Date (date and time), End Date (date and time), Status (numeric).
3. Standard calendar Date table.

Incidents table is linked as 1:* to Statuses table. Date table is standalone and not linked to any table.

The task is to build a clustered column chart with week days on X axis (Sunday to Saturday) and display the numbers (values) of incidents by status and day. Since this task is repeatable for multiple statuses, I just need an assistance in calculating this numbers for one incident status.

Let say it is a number of open incidents per day. Incident is open if its status is less than 9 on the specific day. Therefore, I need to show numbers of open incidents on Sunday, Monday, Tuesday, etc.

I also have a slicer by weeks based on the Date table. User should be able to select any week in current or past year and see the number of open incidents per day on the selected week.

One more. Statuses table might have multiple records with same incident and status, i.e. incident can be closed, reopened, closed again etc. Sometimes multiple activities can happen on the same day, i.e. start and end dates could be equal for multiple statuses of the single incident but the time is different. Therefore, the max status number must be selected according to the date and time.

Example of Statuses table:          

Incident ID          Start Date                                            End Date                                              Status

ABC                        03/23/2020 08:20:12 am              03/23/2020 08:25:24 am              1

ABC                        03/23/2020 08:25:24 am              03/23/2020 10:21:44 am              3

ABC                        03/23/2020 10:21:44 am              03/23/2020 11:25:24 am              9

ABC                        03/23/2020 11:25:24 am              03/23/2020 05:00:01 pm              4  Last record on 03/23

ABC                        03/24/2020 09:10:32 am              12/31/2078 12:00:00 am              10  Incident closed

It means that on Monday 03/24 Incident ABC should be counted as open because of the last status 4.

However, on Tuesday 03/24 it should not be counted as an open. Of course, on any day before 03/23 incident should not be counted at all.

If a using time makes task more complicated then we can skip it and just pull the max status number for a day regardless of time. I can tolerate a slight discrepancy if ABC will be counted as closed (status 9) on 03/23 because of not so many cases like this one but it’s better to do it right.

Thank you very much in advance 😊

[Greg_Deckler]

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[justuser123]

@Greg_DecklerThank you very much. This link gave me some ideas but did not resolve the problem. Besides, my client wants to count incidents by the latest status for the day. Therefore, I'm still working on it. If I could mark your post as a partial solution I would've done it 🙂

[justuser123]

I still need an assistance in this project so I tried to simplify the input data.

The new table MaxStatusPerDay was added into the model.

Table has three columns: Incident ID, Date, MaxStatus and linked to Incidents table as *:1. It looks something like this:

Incident ID                          Date                      MaxStatus

123                                         03/19/2020         4

123                                         03/20/2020         9

123                                         03/22/2020         3

123                                         03/26/2020         9

345                                         03/22/2020         2

345                                         03/25/2020         9

345                                         03/26/2020         5

345                                         03/27/2020         9

The goal is still same: count number of open incidents per day and display it on weekly chart. Weekly chart is based on standalone Date table.

Incident is open when its status is less than 9. Therefore, on 03/19/2020 incident 123 is open, 345 is not counted (did not exist), number of open = 1, and so on:

Date                      Number of Open

03/19/2020         1

03/20/2020         0

03/21/2020         0

03/22/2020         2

03/23/2020         2

03/24/2020         2

03/25/2020         1              345 closed, 123 open

03/26/2020         1              345 open, 123 closed

03/27/2020         0

Any date after 03/27/2020 or before 03/19/2020 the number of open incidents is 0.

Any advice will be greatly appreciated.

Thank you.