Forum Discussion
Helper IIICalculate difference between dates in table and date in measure
In the picture below I am displaying open tickets for our tracker.
One calculation I don't know how to do is:
How do I calculate the average days the open tickets have been open (Ave_days_Open = ???).
I have a measure [Date_Selected] that is populated based on the Chiclet slicers for Year and Month.
For all tickets that were open at the end of the selected Month/Year, I need to calculate the average open days.
In the table: 'Tickets' I have columns for: 'Tickets'[Notification_Date] and 'Tickets'[Resolution_Date].
All tickets appear more than once in the table, because they can have multiple techs assigned, apply to multiple countries, etc.
I can't figure out how to calculate the difference between the 'Tickets'[Notification_Date] of the tickets and the [Date_Selected], where:
'Tickets'[Notification_Date]<[Date_Selected] AND OR('Tickets'[Resolution_Date]>[Date_Selected], Isblank('Tickets'[Resolution_Date]) for distinct [Ticket_Id] in the selected
I think the below should work
average open time =
var chosenDate = [Date_Selected]
return
AVERAGEX(
CALCULATETABLE(
SUMMARIZE( Tickets, Tickets[Id], Tickets[NotificationDate] ),
Tickets[Resolutiondate] > chosenDate || ISBLANK( Tickets[Resolutiondate] )
),
DATEDIFF( Tickets[NotificationDate], chosenDate, DAY)
)
8 Replies
- littlemojopuppy
Community Champion
Hi Robert1981 this wouldn't be too hard except you've got tickets appearing multiple times in the data. Can you share a sample to play with?
Robert1981Not sure how I can share the report. It is linked to SharePoint and there is confidential company information in there
- littlemojopuppy
Robert1981 can you mock up some sample data without anything confidential into another pbix? Save it to OneDrive, Google Drive, Dropbox, etc. and provide a link for download.
johnt75Super User
I think the below should work
average open time =
var chosenDate = [Date_Selected]
return
AVERAGEX(
CALCULATETABLE(
SUMMARIZE( Tickets, Tickets[Id], Tickets[NotificationDate] ),
Tickets[Resolutiondate] > chosenDate || ISBLANK( Tickets[Resolutiondate] )
),
DATEDIFF( Tickets[NotificationDate], chosenDate, DAY)
)- Robert1981
thank you,
although, I don't think this formula takes into consideration the fact that the same [ID] will appear multiple times in the report (once for every consultant assigned, or country impacted). This would skew the average, no?- johnt75
That's what the SUMMARIZE does, it reduces the multiple entries to 1 entry per ticket ID
- Robert1981
It worked perfectly. Thank you!
