Point in Time Comparison

[trb7fh]

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

[trb7fh]

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.

[DataNinja777 · Accepted solution]

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?