Calculate Time Occupied Over Call during an Hour

[Gaurav_Lakhotia]

Hello Everyone,

I'm facing an issue in defining time slot which will give me the time occupied on call during the day.
I've two consultants, one from India and other from USA.

Data View

 I've time of call and the duration of call(in minutes). In very last column I've mentioned the desired time slots.
I want to calculate, on call duration %, time slot wise.

Desired table,

Location	1 PM	2 PM	3 PM	4 PM	5 PM	6 PM	7 PM
India	100 %	75 %	100 %	33.33%	-	-	-
USA	100 %	100 %	83.33%	100 %	25 %	100 %	50 %

Here is the file with Sample Data.

[lbendlin · Accepted solution]

Thank you for providing sample data.

Your desired table outcome ignores the [Date]  column - how are you planning to handle that?  One result per day? Some sort of aggregation?  What if a call runs into a new day?

Also, I don't see any time zone information in your setup. Is that intentional?

First step:  Create a table that has all the columns and rows you want in the output:

Table = CROSSJOIN(VALUES(Call_Detail[Location]),GENERATESERIES(0,23/24,1/24))

Next step: list all the calls for the selected location.

Calls =

var l = SELECTEDVALUE('Table'[Location])

var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)

return COUNTROWS(t)

(the return value is just for validation purposes.  We count 4 calls for India and 6 calls for USA)

Next step is to generate the series for the minute numbers for each call

Calls =

var l = SELECTEDVALUE('Table'[Location])

var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)

var s = ADDCOLUMNS(t,"Mins",COUNTROWS(GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1)))

return CONCATENATEX(s,[Mins],",")

(again CONCATENATEX is for validation purposes)

Then we need to create a similar series for each hour bucket

var h = MAX('Table'[Value])

var ht = GENERATESERIES(h*1440,h*1440+59,1)

And lastly we can see how many of the calls' minutes fall into the selected hour bucket, using INTERSECT()

Calls =

var l = SELECTEDVALUE('Table'[Location])

var ht = GENERATESERIES(max('Table'[Value])*1440,max('Table'[Value])*1440+59,1)

var t = CALCULATETABLE(Call_Detail,Call_Detail[Location]=l)

var s = ADDCOLUMNS(t,"Mins",COUNTROWS(INTERSECT(ht,GENERATESERIES(Call_Detail[Time]*1440,Call_Detail[Time]*1440+Call_Detail[Duration(mins)]-1,1))))

return SUMX(s,[Mins])

I'll leave the percentages calculation up to you.