topic Re: Average Talk Time Issue in DAX Commands and Tips

Average Talk Time Issue

M_B_ — Wed, 27 Oct 2021 13:01:01 GMT

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!

Re: Average Talk Time Issue

Anonymous — Wed, 27 Oct 2021 13:11:20 GMT

Hello M_B_

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

Re: Average Talk Time Issue

M_B_ — Wed, 27 Oct 2021 14:46:20 GMT

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?

Re: Average Talk Time Issue

Anonymous — Wed, 03 Nov 2021 08:59:12 GMT

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

--Nate

Re: Average Talk Time Issue

M_B_ — Thu, 04 Nov 2021 09:23:51 GMT

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.