Calculate difference between dates in table and date in measure

[Robert1981]

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

[johnt75 · Accepted solution]

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]

It worked perfectly. Thank you!

[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

[littlemojopuppy]

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?

[Robert1981]

Not 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.

[Robert1981]

Here is the mocked up report. Thqank you for your help Link here