Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.  

ItemIDOldValueNewValueModifiedDate
aaaNewOpen1/1/2019
aaaOpenInProgress2/1/2019
aaaInProgressHold3/15/2019
aaaHoldInProgress3/16/2019
aaaInProgressClosed3/18/2019
bbb New1/15/2019
bbbNewInProgress3/14/2019
bbbInProgressHold3/15/2019
bbbHoldCanceled3/16/2019
ccc New4/2/2019
cccNewInProgress4/2/2019
cccInProgressClosed4/3/2019

 

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

1 ACCEPTED SOLUTION
jsh121988
Employee
Employee

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.

View solution in original post

1 REPLY 1
jsh121988
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.