Average Talk Time Issue

[M_B_]

Hi

I'm trying to calculate average talk time, and am having issues where the call goes into a new day:

My measure is:

Duration Average =

VAR total_second =

AVERAGEX(

CallSummary,

DATEDIFF(CallSummary[wsp_ucc_AcceptedTime],CallSummary[wsp_ucc_EndTime],SECOND)

)

VAR DAXDay = INT(total_second/(24*60*60))

VAR DAXHours = MOD(INT(total_second/(60*60)),24)

VAR DAXMin = MOD(INT(total_second/60),60)

VAR DAXSec = ROUND(MOD(total_second,60),0)

RETURN DAXDay &":"& DAXHours &":"& DAXMin &":"& DAXSec

Can anybody see what is wrong with my formula?

Thanks!

[Anonymous]

Try RETURN DAXDay&":"&TIME(DAXHours, DAXMin, DAXSecond)

--Nate

[M_B_]

Unfortunately, that still returns strange results:

I can see the second part of the calculation has worked correctly. The top call is indeed 17 minutes long, but the first half of the calculation is still adding almost a month to the call time...

As you can see from the next result down on my screenshot, all my other calls are accurate. It's just when the date changes mid-call it can't work it out.

[Anonymous]

Hello @M_B_ 

Have you tried to substract call end time with call start time and represent the value as seconds?

[M_B_]

Hi @Anonymous 

I thought that was essentially what my DATEDIFF was doing within this sum?

What formula would I need for a straight subtraction End Time-Accepted Time which would return a value in seconds?