cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlfredR
Frequent Visitor

[ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Why was my post marked as spam? Please let me know so I can correct it. Thank you.


Link to post: (Cumulative difference between count of two dates)
by AlfredR


https://community.powerbi.com/t5/Desktop/Cumulative-difference-between-count-of-two-dates/m-p/745028...


<p>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.<br />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:</p><table><tbody><tr><td><p><strong>OpenDate</strong></p></td><td><p><strong>CloseDate</strong></p></td></tr><tr><td><p>10/07/2019</p></td><td><p>11/07/2019</p></td></tr><tr><td><p>10/07/2019</p></td><td><p>&nbsp;</p></td></tr><tr><td><p>11/07/2019</p></td><td><p>11/07/2019</p></td></tr><tr><td><p>11/07/2019</p></td><td><p>12/07/2019</p></td></tr><tr><td><p>13/07/2019</p></td><td><p>12/07/2019</p></td></tr><tr><td><p>13/07/2019</p></td><td><p>13/07/2019</p></td></tr><tr><td><p>13/07/2019</p></td><td><p>19/07/2019</p></td></tr><tr><td><p>14/07/2019</p></td><td><p>13/07/2019</p></td></tr><tr><td><p>14/07/2019</p></td><td><p>13/07/2019</p></td></tr><tr><td><p>14/07/2019</p></td><td><p>14/07/2019</p></td></tr><tr><td><p>15/07/2019</p></td><td><p>14/07/2019</p></td></tr><tr><td><p>15/07/2019</p></td><td><p>&nbsp;</p></td></tr><tr><td><p>15/07/2019</p></td><td><p>15/07/2019</p></td></tr><tr><td><p>15/07/2019</p></td><td><p>15/07/2019</p></td></tr><tr><td><p>16/07/2019</p></td><td><p>15/07/2019</p></td></tr><tr><td><p>16/07/2019</p></td><td><p>15/07/2019</p></td></tr><tr><td><p>16/07/2019</p></td><td><p>16/07/2019</p></td></tr><tr><td><p>16/07/2019</p></td><td><p>&nbsp;</p></td></tr><tr><td><p>17/07/2019</p></td><td><p>17/07/2019</p></td></tr><tr><td><p>17/07/2019</p></td><td><p>17/07/2019</p></td></tr><tr><td><p>18/07/2019</p></td><td><p>17/07/2019</p></td></tr><tr><td><p>19/07/2019</p></td><td><p>18/07/2019</p></td></tr><tr><td><p>19/07/2019</p></td><td><p>&nbsp;</p></td></tr><tr><td><p>19/07/2019</p></td><td><p>&nbsp;</p></td></tr><tr><td><p>19/07/2019</p></td><td><p>19/07/2019</p></td></tr></tbody></table><p><br />My measures are:<br /><br />Tickets Opended = COUNTX(tickets,tickets[OpenDate])<br />Tickets Closed = COUNTX(tickets,tickets[CloseDate])<br /><br />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.<br /><br />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.<br /><br />Any help would be appreciated.</p>


This message has 0 replies


1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

When I open your pbix it prompts that it's created in older version, and not sure whether you could open the pbix which made changes in latest version. try below measure:

Measure = CALCULATE(SUMX(Tickets,[Pending]),FILTER(ALL('calendar'),[Date]<=MAX([Date])),VALUES('calendar'[Date].[MonthNo]))

And find the file here: 

 https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EQsuwAQ2p8dLvSgASWhX8B...

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

View solution in original post

8 REPLIES 8
Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

Please correct me if I go wrong:

Based on your data, you'd like to calculate:

On 10/7/2019, open 2, closed 0, so result should be 2.

On 11/7/2019, Open 2, closed 2, so result should still 2.

On 12/7/2019, Open 0, closed 1, so result should be 1.

On 13/07/2019, Open 3, closed 3, so result should be still 1.

......

if so, please follow below steps:

1.Add a new calculated table and manage the relationship :

Table 2 = CALENDAR(DATE(2019,7,1),DATE(2019,7,20))
8.PNG
2. Add the measures to calculate the opencounts and closecounts per day:
OpenCounts = CALCULATE(COUNTROWS('Table'))
CloseCounts = IF(MAX('Table'[CloseDate])=BLANK(),BLANK(),CALCULATE(COUNTROWS('Table'),USERELATIONSHIP('Table'[CloseDate],'Table 2'[Date])))

LeftcountsPerday = [OpenCounts]-[CloseCounts]
then the final counts is the cumuletive sum of leftcountsPerday:
FinalCounts = SUMX(FILTER(ALL('Table 2'),[Date]<=MAX('Table 2'[Date])),[LeftcountsPerday])
Results shown as below:
9.PNG
Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,
Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
AlfredR
Frequent Visitor

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hello, thanks for the response, I'm trying it out now that I have a jump in month as I'll actually use a montly cut for tickets. I'll let you know how it goes. I did have a measure that worked but it only gave me the current month not cumulative:

 

First I calculated the Opened tickets:

OpenTickets = COUNT(tickets[Ticket Date])
Then I calculated the closed Tickets:
CloseTickets = COUNT(tickets[solvedate])
And then I calculated the difference:
PendingTickets = 
CALCULATE(([OpenTickets] - [ClosedTickets]),
  FILTER(ALLSELECTED(tickets[Ticket Date]),
    tickets[Ticket Date] <= MAX(tickets[Ticket Date])))
 
The problem is that on the jump to august I get a monthly pending and not cummulative.
I'll try your solution to see if it works like that.
AlfredR
Frequent Visitor

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

I can't get it to work, here is my original table with only the related fields.

 

Tickets Table

Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

Please check this :

 

https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EVqEwRvj3ThGm6W6XhHUVz...

 

Note that I convert the date/time to date in query editor to make sure counts based on day rather than time. and set the month as cut.

 

Best regards,
Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
AlfredR
Frequent Visitor

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Here is my file with what I'm at right now based on your help.

 

Opened = COUNT(Tickets[date])
Closed = CALCULATE(
  COUNT(Tickets[solvedate]),
  FILTER(Tickets,Tickets[solvedate] <= MAX('calendar'[Date]))
)
Pending = [Opened] - [Closed]

What I'm missing is making the pending measure cummulative right now I only have it month by month.

 

u9JxrNaM7E.png

 

 

https://files.fm/u/2m5jzvqu

 

Highlighted
Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

When I open your pbix it prompts that it's created in older version, and not sure whether you could open the pbix which made changes in latest version. try below measure:

Measure = CALCULATE(SUMX(Tickets,[Pending]),FILTER(ALL('calendar'),[Date]<=MAX([Date])),VALUES('calendar'[Date].[MonthNo]))

And find the file here: 

 https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/EQsuwAQ2p8dLvSgASWhX8B...

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

View solution in original post

Community Support
Community Support

Re: [ABUSE] By: AlfredR / Board: power-bi-designer (359154)

Hi @AlfredR ,

 

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,
Dina Ye

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors