cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlfredR Frequent Visitor
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
Community Support Team
Community Support Team

Re: Cumulative difference between count of two dates

2 REPLIES 2
Community Support Team
Community Support Team

Re: Cumulative difference between count of two dates

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.

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors