calculate concurrent use with data entry that has a start_time and end_time

[Ferdi_Vissers]

Hi Guys,

Need your help on calculating concurent use of our video-platform. I am importing CDR data true the management API from the system, so far so good. Every evening there will be updated information regarding usage. The columns on where we could get the concurent use on are: start_time and end_time

There is also a column called "service_tag" that I can use to put information like department and would like to use that as a diffirentiator for calulating concurrent use for different departments.

what would be the preferred method for calculating this?

.Ferdi

[v-yuta-msft]

Hi Ferdi_Vissers,

What's your expected result? Could you please provide more details about your table structure and measure or calculate columns?

Regards,

Jimmy Tao

[Ferdi_Vissers]

Hi @v-yuta-msft,

Any chance to help me out with this?

Regards, Ferdi

[v-yuta-msft]

 Hi Ferdi_Vissers,

SUMPRODUCT() in excel is equal to SUMX(Table, Table[Column1] * Table[Column2]).

Regards,

Jimmy Tao

[Ferdi_Vissers]

Hi Jimmy,

My expected result would be that I am able to get a report on the simultaneous calls on our system (total), and break that down per department. I want to make reports on that like this:

• how many concurrent calls where there on a day.
• what where the most busiest times (simultaneous calls at the same time)
• What was the resource usage of a department.
• Which percentage of resources are being used by which department.
• what was the overall usage of the system.

Per row there is a historically overview of just one call. in one row we have columns like: 

"start_time" = start of the call

"end-time" end off the call

"id" = unique identifier for the call

"service_tag" a field that be filled with extra infromation, for example the department name to be used as filter.

I've attached a a example of the whole table https://pastebin.com/5k3vWMp2

Regards, Ferdi

[Ferdi_Vissers]

Hi,

If i needed to this in EXCEL I would use =SUMPRODUCT like:

=SUMPRODUCT(--($AP$1:$AP$1000<=AP2);--($AS$1:$AS$1000>=AP2))

Where "$AP$1:$AP$1000" would be the column with the start_time and "$AS$1:$AS$1000" the end_time.

But how to do this in PowerBi is still unclear. Someone ever dealing with this?

https://1drv.ms/u/s!AocuAXbsG4Bsgirzuw7VFnA9C38J

Regards, Ferdi