I have written at length about the logical approach required to accurately measure how many Incidents (Incident or otherwise) are open during a reporting period without resorting to hideous ‘snap-shot’ reporting.
My other writing has always been SQL-centric, but I recently faced this challenge with Power BI: which required a very different approach, so I thought I’d share!
The data associated with an Incident contains an Open Date and a Resolved Date fields (I’m ignoring Closed Dates, but the logic is the same), if these dates span across two or more months there is no field(s) to capture these point’s in time from which to chart from.
Reporting on what isn’t there is always tricky!
The first choice for this sort of calculation is to push it to the data warehouse to calculate (which is where my earlier writing will help!) but Power BI is everywhere and often used directly from a relational data source or an extracted data set. In many cases, this extra data will have to be generated within Power BI.
Using DAX (Power BI’s built in scripting language) we are going to create a new table derived from a cut down mock-up of an ITIL/Incident extract that could easily be created using ServiceNow’s built in reporting capabilities or that of most other ITSM tools out there.
The bad news is that Power BI DAX is limited in its methods of creating new data, as opposed to summarising or reformatting existing data. To be fair, this should be expected for reporting software!
To keep the focus on the matter at hand, the data set has been kept to the absolute minimum. In the real world, the data set would be far more comprehensive.
First, we need to know how many months (as most ITIL reporting is monthly) an Incident is open before we consider creating the new table. With the DATEDIFF function, this is straight forward until we get to Incidents that are currently open and have no Resolved Date value.
We just compare the Open Date to the Resolved Date using monthly intervals to create a Calculated Field.
For the context of showing a historic trend of open Incidents, an Incident being Resolved after the reporting period is the same thing as it being open during said timeframe. With this in mind, we shall treat the ResolvedDate as the current date for the sake of this calculation, like so:
IF(Sheet1[ResolvedDate] = BLANK(),
DATEDIFF(Sheet1[OpenDate], TODAY(),MONTH) ,
A couple of points to note:
- An Incident that was opened and resolved in the same month will return zero, which is just what we want.
- By treating an unresolved Incident as being resolved on the current date will show the Incident as being open from its inception until the end of the reporting period (I.e.: the last twelve full months).
With this newly created MonthsCount field, we can generate the Calculated Table upon which to populate charts.
To achieve this, we will use the following functions:
Altogether, it looks like this:
OpenTicketDates = GENERATE(
“Value”, [Value], “ChartMonth”, EDATE(Sheet1[OpenDate], [Value])
[Value] <= Sheet1[MonthsCount])
The above code creates a new table and inserts as many rows as identified by the “MonthsCount”, while using EDATE to increment the month for each row.
- DATEADD will not return a result that isn’t in the current date range…it is far easier to use EDATE!
Below is the resulting table which now contains a row for each month it is open:
Fig. 1: The dynamically generated trend table.
Use the Calculated Table to populate the required chart(s).
Below is my end result, presented to show the dynamic trend data, rather than win any design awards:
Fig. 2: The trend data showing the results for the full months in the current year.
Fig. 3: The same charts filtered for the full year.
And that is it!
This solution is simple enough and with only a few gotchas along the way! The most difficult thing is the switch in thinking away from an SQL mindset and into the wonderful world of Power BI’s Excel-esque approach to data management. Personally, I love it! But I’ve been using Excel as a supporting BI tool my entire career and most of Power BI/DAX makes perfect sense to me…and then there is DATEADD!
Of course, the real beauty of this solution is that it is built into the report and works from the original data, thus removing any manual interaction, reliance on a data warehouse or snap-shot reporting.