Forum Discussion

Ferdi_Vissers's avatar
Ferdi_Vissers
Frequent Visitor
8 years ago

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

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

5 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    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's avatar
      Ferdi_Vissers
      Frequent Visitor
      8 years ago

      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's avatar
        Ferdi_Vissers
        Frequent Visitor
        8 years ago

        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

         

      • v-yuta-msft's avatar
        v-yuta-msft
        Icon for Community Support rankCommunity Support
        8 years ago

         Hi Ferdi_Vissers,

         

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

         

        Regards,

        Jimmy Tao