Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 :
Solved! Go to Solution.
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
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
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.
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.
My DAX query result is
not sure what went wrong.
Thanks
@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
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |