Counting Open and Closed Tickets Before Each Ticket's Open Date

[ksuero]

I have been watching videos and reading forum posts regarding this topic, but my implementation is not working with these running sum tutorials.

I have 3 data points: Status(Open/Close), Open Date, Updated Date

* If a ticket is of closed status and has an update, it is closed.

I really need to get the sum of open and closed tickets for the period of data I am importing. 

I need the total on each line. This will hopefully represent the number of tickets that are open at the creation of any given ticket. I am going to do a bar-line graph for my visualization.

Can anyone help me with this?

Thanks!

[Anonymous]

Hi ,

I am looking for a similar solution to this. i need to calculate the open and closed tickets and response taken by each user from open to close based on open date and closed date . 

Unique id , status , opendate,closed date,employee.

@ksuero @v-yulgu-msft @Tim @TimHP 

[v-yulgu-msft]

Hi @ksuero ,

Please provide some dummy data in source table, and illustrate your desired result with examples.

How to Get Your Question Answered Quickly

Best regards,

Yuliana Gu

[ksuero]

Hi @v-yulgu-msft here is a sample data set and an ideal graph.

Tickets, uniquely identified by IDs, should be represented as a memeber in their severity graph if they are of status open. If they are closed, the should be counted in the sum for each month up until their close date.

ID Open Date Update Date Status Severity

1	1-1-2019		open	sev 1
2	1-1-2019	3-2-2019	closed	sev 2

Thank you for offering your help and do let me know if you need anything else to help me.

Thanks again, @v-yulgu-msft!

[ksuero]

@v-yulgu-msft Do you have any idea of how to accomplish this? 

[ksuero]

sum open = CALCULATE(
SUM(tickets[open bool]),
ALL(tickets), tickets[open date] <= EARLIER(tickets[openDate])

Here is what I am currently using. Open Bool is 1 if the ticket is open.

[ksuero]

Right now, my sum is correct for each ticket. If I look at the most recent ticket, the previous logic correctly inserts the count of all open tickets. My problem is that the graph seems to depict a sum of each sum in each ticket for each month. I was each value to be represented rather than a sum of the values.

I believe that this is showing the sum of the count at each ticket. I just want to show the sum at every ticket in the time period or the highest number in that group of tickets as that will give a more accurate reflection of ticket activity this month.