Report on open tickets, closed and raised in a given period

[Anonymous]

Hi everyone,

I have been searching the forums and Google, while I have found applicable answers I haven't been able to compile a complete solution for this. Hoping someone can help.

I am needing to report on the number of raised, resolved and open tickets in a given period. The period could be based on a day, a weekly period or by month for example - I tend to use appropriate date slicers to filter graphs/tables.

I have three tables:

• Date table - contains unique dates (no timestamp)
• Combined ticket table - list of all tickets in the database which records the created date and resolved date
• Closed combined ticket table - duplicate of the above table

The date table has a relationship to the 'combined ticket' table to the column 'created date'. The date table also has a relationship to the 'closed combined ticket' table to the column 'resolved date'.

Using a line graph I have been able to show tickets resolved and raised grouped by week by:

• Using the Date table as the bottom Axis of the line graph
• Add 'created date' as a value and 'resolved date' as a value. 

There's likely an easier way to do this without duplicate the tables to count the number of resolved tickets based on the resolved date but this was the quickiest solution I could find.

What I want to add into the above is a line showing the open tickets at that time. So for the week of 29/4/18, if a ticket did not have a 'resolved date' appearing on or before that week, show it as an 'open' ticket. 

I have read about 'events in progress' but really struggling to understand how to embed that in. I think part of the problem I have is the duplication of the table to get the resolved ticket count.

Any ideas?

[v-chuncz-msft · Accepted solution]

@Anonymous,

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/calculate-open-tickets-by-date/m-p/207644#M91517