A simple question I hope- - How to process a Ticket Audit table

[Anonymous]

Hello,

I have a simple question, hopefully...  Looking for some advice and examples.  Seems like basic Ticket history reporting and calculations...   This is not rocket science and not unique to our business.. Any examples on how others manage typical Ticket/issue data and reporting.. 

We are using MS Dynamics for Customer Tickets, highly customized.  🙂 and extracting to Power BI for Reports and Dashboards.

The Ticket table has the "current" ticket data with typical fields like ItemID, CreatedDate, ClosedDate, Status,Owner, Customer, etc...  

I have created measures to calculate the "backlog" based on Open and Closed date with a Calculated Date table in my data set.  I have measures for the Number of Open, the number of Closed and the Variance (num_opened_vs_Closed).. then use this formula for the backlog.

• Backlog = CALCULATE([Num_Opened_vs_Closed],FILTER(all(Table_Dates[Date]),'Table_Dates'[Date]<=max(Table_Dates[Date])))

What I need to be able to do is to calculate the number of Tickets in any Status at any given point in time.

So how many were "inprogress" on 2/10/2019.. How many were New in the month of March?

Also to be able to calculate the duration in any given status and store back with the Ticket itself.

There are other "attributes" that are captured in our Audit table but this is just the audit trail for "Status".. 

Below is an example of a view of the audit table looks like for the "Status" field.  

ItemID	OldValue	NewValue	ModifiedDate
aaa	New	Open	1/1/2019
aaa	Open	InProgress	2/1/2019
aaa	InProgress	Hold	3/15/2019
aaa	Hold	InProgress	3/16/2019
aaa	InProgress	Closed	3/18/2019
bbb		New	1/15/2019
bbb	New	InProgress	3/14/2019
bbb	InProgress	Hold	3/15/2019
bbb	Hold	Canceled	3/16/2019
ccc		New	4/2/2019
ccc	New	InProgress	4/2/2019
ccc	InProgress	Closed	4/3/2019

Thanks in advance for any advice and examples and help... 

[jsh121988 · Accepted solution]

This returns the count where Table_Dates[Date] is GtE CreatedDate and Lt ClosedDate. MIN is used in both intentionally because your specifying an exact point in time. This can be used on a hierarchy as well to say "At the very beginning of June, here was our active ticket count".

Backlog =
CALCULATE(
    COUNTROWS(Tickets),
    FILTER(Tickets,
        Tickets[CreatedDate] <= MIN(Table_Dates[Date])
        && MIN(Table_Dates[Date]) < Tickets[ClosedDate]
    )
)

If this runs too slow, you may want to consider returning a backlog table where you join your data to a date table in sql with the same intersecting logic with an inner join.