Open Tickets per group by date

[speedster]

Similar to an earlier question (sales by product by date), I am working with some data from ServiceNow and want to display Open Tickets per group per date. Data model is as follows:

The measures created on the Incident table are as follows:

OpenTickets = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]<=max(DateDimension[Date]) && (Incident[resolved_at_date] > MAX(DateDimension[Date]) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsFilter = CALCULATE(COUNTROWS(FILTER(ALLSELECTED(Incident), Incident[opened_at_date]<=max(DateDimension[Date]) && (Incident[resolved_at_date] > MAX(DateDimension[Date]) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsPrev = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]<=(max(DateDimension[Date])-1) && (Incident[resolved_at_date] > (MAX(DateDimension[Date])-1) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsFilterPrev = CALCULATE(COUNTROWS(FILTER(ALLSELECTED(Incident), Incident[opened_at_date]<=(max(DateDimension[Date])-1) && (Incident[resolved_at_date] > (MAX(DateDimension[Date])-1) ||ISBLANK(Incident[resolved_at_date])))))

OpenTicketsDelta = [OpenTickets] - [OpenTicketsPrev]

TicketsOpened = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[opened_at_date]=max(DateDimension[Date]))))

TicketsResolved = CALCULATE(COUNTROWS(FILTER(ALL(Incident), Incident[resolved_at_date]=max(DateDimension[Date]))))

The *filter measures are for use when using a slicer to filter for a specific period. , whereas the other measures are used for when I want to view point-in-time cumulative numbers.

What I need to be able to do is report on the count of cumulative opened tickets, tickets opened, tickets resolved per day by things like:

• The group or agent the ticket is assigned to
• The agent that resolved the ticket
• The location of the caller
• The department of the caller

If I create a matrix with Date, Assignment Group and open tickets this is what I get:

I can create measures that contain hard-coded filters and use those, but it is not scalable (I don't want to create/remove measures as agents join/leave). How can I report on the data I require? I think I might have to query the data into a new table and use that for the visualisation source?

Thanks in advance.

[konstantinos · Accepted solution]

This is called count "events in progress". ( so you can google it )

First you cannot have an active relantionship between the Dates table and the inccident table, so probably you need to create an extra date table or make all realntionships inactive in the one that you use.

If you have an active relantionship you will count the tickets open this day and not the actual tickets that are open also from previous days.

For solutions you can check these posts from @GBrueckl & @Technitrain

 http://blog.gbrueckl.at/2014/12/events-in-progress-for-time-periods-in-dax/

http://blog.crossjoin.co.uk/2011/11/10/solving-the-events-in-progress-problem-in-dax-v2-0/

and check this whiteparer after 16 page http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf from Alberto Ferrari