Calculating Average resolution time in the last 30 days

[Radhika_Kanaka]

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

[tamerj1]

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

[Radhika_Kanaka]

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

[tamerj1]

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. 

[Radhika_Kanaka]

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.

[tamerj1 · Accepted solution]

@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

[Radhika_Kanaka]

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 

[amitchandak]

@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