Time difference from same column

[Daven]

Hi,

I have call details table and want to see avg time difference if "called from" is same number. I.e for below, avg time difference for called from # 1 is 3 hrs.

Call start time	# called from	Avg call time diff (HH: MM)
11/9/2019 15:45	1	3 hrs
11/9/2019 20:45	1	3 hrs
11/9/2019 21:45	1	3 hrs
11/9/2019 06:10	2	0 hrs
11/9/2019 7:20	3	o hrs

Thanks in advance 

[dax · Accepted solution]

Hi Daven, 

It seems that you want to getAbvg call time value, right? If so, you could refer to my sample. If not, please  correct me and inform me more detailed information (such as your sample data and your expected output), then I will help you more correctly.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daven]

Hi Zoe, 

Your solution works. Thank you.

Is it possible to get avg time diff only of the call start time is on same date? Otherwise null result

I added a different date in the sample data here. https://drive.google.com/file/d/1-yejDy7-ooElRygQ-0N-vOlf4jPOynYr/view?usp=sharing

Thanks,

Daven

[dax]

Hi Daven, 

You could create calculated column like below , then use below measure to see whether it work or not

Column = T3[Call start time].[Year]*10000+T3[Call start time].[MonthNo]*100+T3[Call start time].[Day]

Measure 4 = VAR COUNTAMOUNT=CALCULATE(COUNT(T3[Call start time]),FILTER(ALL(T3), T3[Column]=MIN(T3[Column]))) VAR MAXT=CALCULATE(MAX(T3[Call start time]), FILTER(ALL(T3), T3[Column]=MIN(T3[Column]))) VAR MINT=CALCULATE(MIN(T3[Call start time]),FILTER(ALL(T3), T3[Column]=MIN(T3[Column])))  RETURN IF(COUNTAMOUNT>1, DATEDIFF(MINT,MAXT,HOUR)/(COUNTAMOUNT-1),0)

 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bchager6]

Here's a simple and straightforward solution with the results shown in slicers and a table:

Using this measure: 

Avg. Time Difference = AVERAGE ( 'Table'[Avg call time diff (HH: MM)])

Slicer

Slicer

Table

[parry2k]

@Daven Seems like data is not complete, you have call start time but no end time, how you calculated the avg?