Converting number to Hours and Minutes in DAX

[Radhika_Kanaka]

Hi,

My goal is to find the average resolution time for the incidents. Below are the DAX queries. I am getting incorrect Average resolution time.  The correct average resolution time is 31:57 (HH:MM). My result is 12:05 (HH:MM). Please let me know if I am missing anything here.

1. I converted Time coumn to round to the nearest minutes . 

MinutesRoundTotal =

60 *HOUR(Source[To Resolution (Business)]) + MINUTE ( MROUND ( Source[To Resolution (Business)], TIME ( 0, 1, 0 ) ) + TIME ( 0, 0, 0 ) )

2. Calculate average and convert it into hours and minutes. ( Considering the tickets that are resolved in the last 30 days)

Medium_Resolution_Timestamp =

Var ART=calculate(AVERAGE(Source[MinutesRoundTotal]),filter(source,Source[Priority]="Medium"),DATESINPERIOD('Source'[Resolved At].[Date],MAX(Source[Resolved At]),-30,DAY))

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

Thanks,

Radhika

[tamerj1]

Hi @Radhika_Kanaka 

DATESINPERIOD is a time intelligence function that works only with standard date table that is marked as date table. 

[Radhika_Kanaka]

@tamerj1  Thank you for the response. What can I use instead of DATESINPERIOD for calculating average resolution time for the last 30 days (time is based on Resolved At date/Time field.) 

[tamerj1]

Hi @Radhika_Kanaka 

you may try
'Source'[Resolved At] >= MAX(Source[Resolved At]) -30,

'Source'[Resolved At] <= MAX(Source[Resolved At]),