Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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...
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |