Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

Calculating Average resolution time in the last 30 days

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
1 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

View solution in original post

7 REPLIES 7
tamerj1
Super User
Super User

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

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

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. 

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.

@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

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.

 

Radhika_Kanaka_0-1652408999574.png

 

My DAX query result is 

 

Radhika_Kanaka_1-1652409059148.png

not sure what went wrong.

 

Thanks 

amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.