Forum Discussion
Point in Time Comparison
Hello,
I've searched the forums for a while and can't find anything that seems to be a match to what I'm doing, but maybe I'm not searching for the right thing.
I'm needing to build a visual that will show a count how many open incidents we have on the first of the month for the last 12 months. Open being defined as CREATEDDATE is equal to or before the as-of-date (the 1st of the corresponding month), and RESOLVEDDATE is after the as-of-date or null. If possible, we'd like to count anything with a status of "Reopened" as Open, despite the RESOLVEDDATE showing an earlier value, but these tickets are few enough I believe we can manage without if needed. We'd also like a separate count of the number of tickets Open as of today.
I've attached a sample of the (scrubbed) data, which is imported from our database. The table name is CHERWELL_INCIDENT_TRACKING, and it's the only table I have on the report. I don't have any date tables or other items in modeling. Note we are looking for a distinct count, but INCIDENTID is our primary key with each incident having its own row and listed once.
Thank you in advance for the help! I know how to build this in my old software but still very new to Power BI.
| INCIDENTID | CREATEDDATE | STATUS | LASTMODIFIEDDATE | RESOLVEDDATE | ESCALATED |
| 928328 | 10/24/2021 | Assigned | 2/18/2022 | null | Yes |
| 931528 | 10/27/2021 | Assigned | 4/13/2023 | null | No |
| 931532 | 10/27/2021 | Assigned | 4/13/2023 | null | No |
| 931536 | 10/27/2021 | Assigned | 4/13/2023 | null | No |
| 924193 | 10/19/2021 | Assigned | 10/19/2021 | null | Yes |
| 932453 | 10/27/2021 | Assigned | 4/13/2023 | null | No |
| 17268 | 10/1/2018 | Closed | 5/29/2023 | 5/18/2023 | No |
| 19185 | 10/2/2018 | Closed | 9/26/2022 | 9/16/2022 | No |
| 19187 | 10/2/2018 | Closed | 10/3/2022 | 9/22/2022 | No |
| 50100 | 11/19/2018 | Closed | 10/6/2022 | 9/26/2022 | No |
| 52899 | 11/27/2018 | Closed | 8/10/2023 | 7/31/2023 | Yes |
| 177433 | 6/7/2019 | In Progress | 3/22/2021 | null | Yes |
| 186454 | 6/19/2019 | In Progress | 8/29/2023 | null | No |
| 207478 | 7/18/2019 | In Progress | 3/22/2021 | null | Yes |
| 923515 | 10/18/2021 | New | 10/18/2021 | null | No |
| 134611 | 4/8/2019 | Pending | 2/23/2022 | null | Yes |
| 161630 | 5/20/2019 | Pending | 3/22/2021 | null | Yes |
| 312740 | 11/13/2019 | Pending | 8/22/2023 | null | No |
| 419555 | 3/26/2020 | Reopened | 3/22/2022 | 3/18/2022 | No |
| 810335 | 6/22/2021 | Reopened | 2/9/2022 | 2/3/2022 | No |
| 807258 | 6/16/2021 | Reopened | 2/9/2022 | 2/3/2022 | No |
| 128337 | 3/29/2019 | Resolved | 8/31/2023 | 8/31/2023 | No |
| 945734 | 11/10/2021 | Resolved | 8/25/2023 | 8/25/2023 | Yes |
Hi trb7fh
I've prepared your required output in the power BI data model visualization as shown below. In Power BI, the best practice is to utilize data model instead of combining in one table all the data. For this type of requirement where there are two dates columns in a fact table to calculate duration, the standard way to solve is to use a disconnected calendar (date) dable.
First, I created a separate calendar table by writing the following dax table formula.
Additionally, I created a calculated column to distinguish the 1st date in a month and other days in the calendar table (I am sure there's a way to combine this in the table formula above, but I couldn't do it quickly, hence a calculated column was created).
The data model looks like below where there are no relationship between the fact table and the calendar table (it's called a disconnected table).
In order to express the open items with respect to the time dimension, I've written dax measure like below:
Additionally, I've written a list of concatenatedXed ID to identify which ID are open at any particular date which can be cross filtered by the month and year matrix table visualization.
The resultant visualization is as shown below, where you can toggle between 1st day and other slicer to get beginning of the month and end of the month status.
I attach the pbix file link below:
CHERWELL_INCIDENT_TRACKING.pbix
BTW, just out of curiosity, is your old system Tableau?
2 Replies
Hi trb7fh
I've prepared your required output in the power BI data model visualization as shown below. In Power BI, the best practice is to utilize data model instead of combining in one table all the data. For this type of requirement where there are two dates columns in a fact table to calculate duration, the standard way to solve is to use a disconnected calendar (date) dable.
First, I created a separate calendar table by writing the following dax table formula.
Additionally, I created a calculated column to distinguish the 1st date in a month and other days in the calendar table (I am sure there's a way to combine this in the table formula above, but I couldn't do it quickly, hence a calculated column was created).
The data model looks like below where there are no relationship between the fact table and the calendar table (it's called a disconnected table).
In order to express the open items with respect to the time dimension, I've written dax measure like below:
Additionally, I've written a list of concatenatedXed ID to identify which ID are open at any particular date which can be cross filtered by the month and year matrix table visualization.
The resultant visualization is as shown below, where you can toggle between 1st day and other slicer to get beginning of the month and end of the month status.
I attach the pbix file link below:
CHERWELL_INCIDENT_TRACKING.pbix
BTW, just out of curiosity, is your old system Tableau?
Hello!
I sincerely appreciate the thorough response. Max Date was missed, but I was able to figure it out easily from your test file. I've also included it below in case anyone comes across this thread in the future looking for answers. Otherwise, I was able to recreate it in our file and it works perfectly! I figured a date table would be needed, since the source table was created by an outside source (and all existing in one table), but I'm not very familiar with them and had only seen them as a connected table so I wasn't sure how to go about building for this particular question.
The old system from my previous job was Looker.
