Calculate Daily Average Waittimes

[rggnkmp]

I have a list of incident reports that contain a creation date and a closure date.

I already created a CALENDAR-table (Incident Load) for each date (MIN to MAX) of the reporting period.

Now I need to calculate the AVERAGE wait time (= not yet closed incident report) for each day of all reports, that have not yet been closed.

I can count them easily   

Open Incidents = CALCULATE(
  COUNT('Incidents'[id]),
    FILTER('Incidents',
      'Incidents'[creationDate].[Date] < 'Incident Load'[Date].[Date] && 
      ('Incidents'[closureDate].[Date] > 'Incident Load'[Date].[Date] || ISBLANK('Incidents'[closureDate].[Date]))
    )
)

but I cannot calculate the average datediff per day.

[anmolmalviya05]

Hi ,

To calculate the average wait time per day for open incidents, you can create a DAX measure that calculates the DATEDIFF for each open incident on each day and then finds the average. Since you’ve already calculated the open incidents per day, we’ll use a similar approach to determine the number of days each incident has been open on a given day. Here’s how to set it up:

Step 1: Calculate the Number of Days an Incident Has Been Open on a Given Date

We’ll start by creating a measure that calculates the days an incident has been open as of each date.

Days Open =

VAR CurrentDate = 'Incident Load'[Date].[Date]

RETURN

CALCULATE(

AVERAGEX(

FILTER(

'Incidents',

'Incidents'[creationDate] <= CurrentDate &&

(ISBLANK('Incidents'[closureDate]) || 'Incidents'[closureDate] > CurrentDate)

),

DATEDIFF('Incidents'[creationDate], COALESCE('Incidents'[closureDate], CurrentDate), DAY)

)

)

Now that you've created the Days Open measure, you can use it in a line chart or table visual. Add Incident Load[Date] as the axis (or row) and Days Open as the value. This will give you the average wait time per day across all incidents that were open on each date.

This approach calculates the average open time on each day based on open incidents, providing an accurate daily measure of your average wait time.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in

@rggnkmp

[Anonymous]

Hi @rggnkmp ,

One question, based on the dax expression you provided, how do I calculate the difference in days if the “close date” is empty? This affects the calculation of the average.

ISBLANK('Incidents'[closureDate].[Date]

Best Regards,
Wenbin Zhou

[Kedar_Pande · Accepted solution]

@rggnkmp 

Measure

Average Wait Time = 
VAR CurrentDate = 'Incident Load'[Date]
VAR OpenIncidentsTable = 
FILTER(
'Incidents',
'Incidents'[creationDate] <= CurrentDate &&
(ISBLANK('Incidents'[closureDate]) || 'Incidents'[closureDate] > CurrentDate)
)
VAR DateDiffTable = 
ADDCOLUMNS(
OpenIncidentsTable,
"DaysOpen", DATEDIFF('Incidents'[creationDate], CurrentDate, DAY)
)
RETURN
AVERAGEX(DateDiffTable, [DaysOpen])

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[FreemanZ]

hi @rggnkmp ,

try to write a calculated column like:

average datediff per day =

AVERAGEX(

    FILTER('Incidents',

      'Incidents'[creationDate]< 'Incident Load'[Date] && 

      ('Incidents'[closureDate] > 'Incident Load'[Date]|| ISBLANK('Incidents'[closureDate]))

    ),

   DATEDIFF(Incidents[creationDate], 'Incident Load'[Date], DAY)

)