cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative difference between count of two dates

I have something that I haven't been able to solve trying to get information from a ticketing system. I have a table with two date fields, opendate and close date.
I have made two measures to calculate the count of dates for each field so that I can know based on that count, in a period of time how many tickets where opened and how many where closed. Here is an example of the data:

 OpenDate CloseDate 10/07/2019 11/07/2019 10/07/2019 11/07/2019 11/07/2019 11/07/2019 12/07/2019 13/07/2019 12/07/2019 13/07/2019 13/07/2019 13/07/2019 19/07/2019 14/07/2019 13/07/2019 14/07/2019 13/07/2019 14/07/2019 14/07/2019 15/07/2019 14/07/2019 15/07/2019 15/07/2019 15/07/2019 15/07/2019 15/07/2019 16/07/2019 15/07/2019 16/07/2019 15/07/2019 16/07/2019 16/07/2019 16/07/2019 17/07/2019 17/07/2019 17/07/2019 17/07/2019 18/07/2019 17/07/2019 19/07/2019 18/07/2019 19/07/2019 19/07/2019 19/07/2019 19/07/2019

My measures are:

Tickets Opended = COUNTX(tickets,tickets[OpenDate])
Tickets Closed = COUNTX(tickets,tickets[CloseDate])

I calculate by date the tickets not solved in a period by substracting those two measutres. Now what I'm trying to acomplish now is keep track of the sum of difference of those counts over time. Let's say that on day one I had one ticket not closed, and day two I had another ticket not closed, the pending tickets should be two, not one. And if on day three I have 5 tickets opened and 3 closed that two not closed should sum with the two remaining from other periods so I have 4 tickets pending. If on a fourth day I have 3 tickets opened and 4 closed then the total pending should be 3 because I closed an older ticket.

I haven't been able to figure out how to do that. I'm visualizing this data in a column graph where the values are those measures and the axis is the date of the ticket creation.

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

## Re: Cumulative difference between count of two dates

You may refer to the following posts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support

## Re: Cumulative difference between count of two dates

You may refer to the following posts.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper V

## Re: Cumulative difference between count of two dates

May be create a conditional column for closed Tickets and Open ticket based on Closed Date.

So If closed date is not null them Ticket is closed and display 1 and name  this column as ClosedTicketCounts

Similar way If Closed Date is Null them display 1 and Rename this OpenTicketCounts.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors