cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rogerh
Helper II
Helper II

Average amount of days open tickets have been open for - on given date

Good afternoon,

 

I am trying to solve a problem I hope the community can help with. We are using Zendesk for customer serives tickets and I have been asked to create table showing, on day, how many tickets were open, solved est. These have been done but I have been asked to do the following and can not work out the best way of doing it.

 

For the day/period in query, the tickets that are NOT solved in that period, the total number of days of all those tickets between the ticket creation time and the ticket solved time with the result divided by the number of tickets NOT solved in that period.

 

So in short, the avergae time all of the open ticket were open for on a given day.

 

I have a custom table with a list of the dates and have added the ones that were open and solved on the day.

 

Below left, how my tickets table looks. Below Right, what i want to report back. Example: On the 03/08 there were 5 tickets open, two were open for two days and three were open for just one. So thats 7 days and 5 ticket with the average of 1.4 days open.

 

Ticket Metrics
Ticket IDCreated AtSolved At
12202/08/202004/08/2020
12302/08/202004/08/2020
12403/08/202004/08/2020
12503/08/202004/08/2020
12603/08/202004/08/2020
12704/08/2020 
12804/08/2020 
12904/08/2020 

 

Table - On Day
DateAvergae Open Time
02/08/20201
03/08/20201.4
04/08/20201

 

Can anyone help me with a custom to do this please? Or if you have done something simlar in the past, offer a surgestion? 

Thanks

 

1 ACCEPTED SOLUTION

@Rogerh - Maybe try:

 

Measure = 
  VAR __Date = MAX('Table'[Created At])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Created At]<=__Date&&([Solved At]>__Date || ISBLANK([Solved At]))),[Ticket ID],[Created At])
  VAR __Table1 = ADDCOLUMNS(__Table,"Length",(__Date - [Created At])+1*1.)
  VAR __Average = AVERAGEX(__Table1,[Length])
RETURN
  IF(ISBLANK(__Average),0,__Average)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

14 REPLIES 14
djmenon21
Helper III
Helper III

Hi,

 

Im trying to do a similar thing as mentioned as the subject line where in Im trying to find out "Average age days is a time from issue creation till now for open issues." Im trying this for Jira issues which is connected to PowerBi.

 

I have created date, start date, resolution date, end date. I want to find the the Average days from issue creation date till now for open issues. How can I do this. When tried the above measure I was not sure what _Date is here? is it the Created Date or the Resolved Date? Please help

amitchandak
Super User
Super User

@Rogerh , Try with a date calendar

measure= 
averageX(values(Date[date]),
calculate(datediff(min(Created At), coalesce(max(Table[Solved At]),max(Date[Date])),Day),filter(Table, table[Created At]<=max(Date[Date])  && (isblank(Table[Solved At]) || Table[Solved At]>=max(Date[Date])))))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Thank you for your reply @amitchandak, I got the measure to work with my tables but I think I may have not explained it very well. When looking at yesterday I would like to see the average time, all open tickets that are were still open on that day have been open for, on that date.

 

Your code gives me 0 yesterday, 1 the day before, 2 the day before that.

 

We have about 500 tickets open at one time with an average completion time of 7 days (according to Zendesk). So I am exspecting to see alot of 7s with some higher where a tickets have take well over the 7 days. Cheers

@Rogerh - Got it working like this:

Measure = 
  VAR __Date = MAX('Table (4)'[Created At])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table (4)'),[Created At]<=__Date&&[Solved At]>__Date),[Ticket ID],[Created At])
  VAR __Table1 = ADDCOLUMNS(__Table,"Length",(__Date - [Created At])+1*1.)
RETURN
  AVERAGEX(__Table1,[Length])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi @Greg_Deckler, thank you again. This seems really close but not quite it.

 

All of the totals are slightly out but not by far. It does however fall apart the further down the table I get. For example, I had a ticket open from the 9th of March until the 24th. Using your measure against the date from my calendar table the 13th is blank.

 

Could you please have a look and see if your measure picks up every open ticket, on each day its open, showing how long it has been open for on that day (then averages)?

 

Also, yesterday is blank for me. The max date on my tables is yesterday, not today. Is there a change we can make so it shows what happened yesterday please?

 

Thanks for your help!

Hi @Greg_Deckler,

 

Sorry I should watch my wording, what I meant was the output from the measure is slightly incorrect. 

 

For example, I used your code with the table in my request. For the 3rd, I got 1.5 and not 1.4  - (7 days divided by 5 tickets = 1.4)

 

Created atAverage time open tickets have been open for - MeasureMy Maths
02/08/202011
03/08/20201.51.4

 

Also the 4th is not showing. 

 

Is this what you meant in your reply? If not, could you please have a look at this? Apologies I am not very adept when it comes to Dax and very much appreciate your help!

 

Thanks

@Rogerh - Difficult to say, I have uploaded my working PBIX file where I get 1.4.

 

The reason 8/4 doesn't show up is that the measure evaluates to blank for that date given the existing data. I modified the measure to return 0 instead.

Measure = 
  VAR __Date = MAX('Table'[Created At])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Created At]<=__Date&&[Solved At]>__Date),[Ticket ID],[Created At])
  VAR __Table1 = ADDCOLUMNS(__Table,"Length",(__Date - [Created At])+1*1.)
  VAR __Average = AVERAGEX(__Table1,[Length])
RETURN
  IF(ISBLANK(__Average),0,__Average)

 Attached below my sig 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hi @Greg_Deckler 

 

For some reason your code was working perfectly for old data but not for new. It took me a while but I think I have found why. I should have included this in my inital brief so sorry about that.

 

The calculation needs to include the tickets that are still open. I belive your code is only taking into consideration the tickets with a solved date. Can it be edited to add the amount of time the open tickets have been open for, on that day, into the average please? 

 

Thanks

Roger

 

Good Morning @Greg_Deckler and @amitchandak 

 

I am still stuck on this one. Could you please have a second look?

 

Thanks

Roger

@Rogerh - Maybe try:

 

Measure = 
  VAR __Date = MAX('Table'[Created At])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Created At]<=__Date&&([Solved At]>__Date || ISBLANK([Solved At]))),[Ticket ID],[Created At])
  VAR __Table1 = ADDCOLUMNS(__Table,"Length",(__Date - [Created At])+1*1.)
  VAR __Average = AVERAGEX(__Table1,[Length])
RETURN
  IF(ISBLANK(__Average),0,__Average)

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler Worked perfectly, thank you very much!

@Rogerh - Totals are a differe problem entirely, see my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

Seems like I need a larger dataset to test with.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Greg_Deckler
Super User
Super User

@Rogerh - Seems like something along the lines of:

 

Measure =
  VAR __Date = MAX('Calendar'[Date])
  VAR __Table = SUMMARIZE(FILTER(ALL('Table'),[Created At]<=__Date&&[Solved At]>__Date),[Ticket ID],"Length",(__Date - [Created At])*1.)
RETURN
  AVERAGEX(__Table,[Length])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Thanks @Greg_Deckler, I have tried this but where I have put created at_date in bold it doesnt allow me to add a column, it's looking for a measure. Have I done something incorrectly? Thanks

 

Average_Open_Tickets_Open_For =
VAR __Date = MAX('Zendesk - Tickets on Day'[Date])
VAR __Table = SUMMARIZE(FILTER(ALL('ZenDesk - Ticket_Metrics'),[Created at_Date]<=__Date && 'ZenDesk - Ticket_Metrics'[Solved at_Date.1]>__Date),[Ticket ID],"Length",(__Date - [Created at_Date])*1.)
RETURN
AVERAGEX(__Table,[Length])

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors