topic Calculating Average resolution time in the last 30 days in DAX Commands and Tips

Calculating Average resolution time in the last 30 days

Radhika_Kanaka — Fri, 13 May 2022 00:47:53 GMT

Hi,

I am trying to Calculate Average Resolution time for the last 30 days. Can someone please help on achieving this?

DAX query I am using is :

Resolution_Time =

Var ART= Calculate(AVERAGE(incidents[Resolved At]),filter(incidents,incidents[priority]="Medium"),not(incidents[Category]="Not Set" || incidents[Category]="Alerts" || incidents[Category]="Out of Scope"),DATEADD(incidents[CurrentDate],-30,DAY),'Calendar'[WeekDay- Weekend]="Weekday")

Var Result=FORMAT(ART, "HH:MM")

Return ART

Appreciate your help.!!

Thanks

Re: Calculating Average resolution time in the last 30 days

amitchandak — Fri, 13 May 2022 01:26:52 GMT

Radhika_Kanaka , Try like

Resolution_Time =
Var ART= Calculate(AVERAGE(incidents[Resolved At]),filter(incidents,incidents[priority]="Medium"),not(incidents[Category]="Not Set" || incidents[Category]="Alerts" || incidents[Category]="Out of Scope"),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY),'Calendar'[WeekDay- Weekend]="Weekday")
Var Result=FORMAT(ART, "HH:MM")
Return ART

Re: Calculating Average resolution time in the last 30 days

Radhika_Kanaka — Fri, 13 May 2022 02:31:40 GMT

Hi amitchandak , thank you for your quick response. the dax query is still resulting in a different answer.

My service desk report displays the below value.

My DAX query result is

not sure what went wrong.

Thanks

Re: Calculating Average resolution time in the last 30 days

tamerj1 — Fri, 13 May 2022 02:51:51 GMT

Hi Radhika_Kanaka

incidents[Resolved At] is a date/time column. There musy be another column tgat indicated the start date/time in order to measure the difference in time

Re: Calculating Average resolution time in the last 30 days

Radhika_Kanaka — Fri, 13 May 2022 03:54:40 GMT

Hi tamerj1 ,

Thank you for your response. Could you please elaborate on that with an example. I am sorry , I quite didn't understad that.

Thanks

Re: Calculating Average resolution time in the last 30 days

tamerj1 — Fri, 13 May 2022 04:14:38 GMT

Hi Radhika_Kanaka

I believe you are trying to calculate a sort of average processing time. But your calculation is actually averaging a date time which does not make a lot of sense. You should be avergaing for example an integer value that represents the number of minutes. In order to get this number we need to calculate the difference between two date time values (either two columns or a column vs a refrence value like NOW( ) for example).
If you can provide some sample data we may be able to assist you further with this.

Re: Calculating Average resolution time in the last 30 days

Radhika_Kanaka — Fri, 13 May 2022 16:05:03 GMT

Yes, I am using Resolved-At and Created-At dates for finding average resolution time.

I created a calculated column to find the resolution time with the below DAX query.

custom-resolution-time = DATEDIFF(incidents[Resolved At],incidents[Created At],DAY)

I am then creating a measure to find the average resolution time for the last 30 days.

Medium_Resolution_Timestamp =

Var ART=Calculate( AVERAGE(incidents[custom-resolution-time]),filter(incidents,incidents[priority]="Medium"),not(incidents[Category]="Not Set" || incidents[Category]="Alerts" || incidents[Category]="Out of Scope"),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-30,DAY),'Calendar'[WeekDay- Weekend]="Weekday")

Var Result=if(isblank(ART),"No Data Available" & UNICHAR ( 10 ) &"Data displayed is: last 30 days since" & " (" & (today()-30) &")"& ". Priority is: Medium ",FORMAT(ART,"HH:MM" )& UNICHAR ( 10 ) & UNICHAR ( 10 ) & "HH:MM")

Return UNICHAR(10)

& Result

Please let me know if there is anything to be modified.

Re: Calculating Average resolution time in the last 30 days

tamerj1 — Fri, 13 May 2022 16:20:27 GMT

Radhika_Kanaka

For the column return the minutes

custom-resolution-time = DATEDIFF(incidents[Resolved At],incidents[Created At],MINUTE)

The measure contains to many filters which I assume are correct. The

Medium_Resolution_Timestamp = VAR ART = CALCULATE ( AVERAGE ( incidents[custom-resolution-time] ), FILTER ( incidents, incidents[priority] = "Medium" ), NOT ( incidents[Category] = "Not Set" || incidents[Category] = "Alerts" || incidents[Category] = "Out of Scope" ), DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -30, DAY ), 'Calendar'[WeekDay- Weekend] = "Weekday" ) VAR Result = IF ( ISBLANK ( ART ), "No Data Available" & UNICHAR ( 10 ) & "Data displayed is: last 30 days since" & " (" & ( TODAY () - 30 ) & ")" & ". Priority is: Medium ", QUOTIENT ( ART, 60 ) & ":" & MOD ( ART, 60 ) & UNICHAR ( 10 ) & UNICHAR ( 10 ) & "HH:MM" ) RETURN UNICHAR ( 10 ) & Result