Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Measure - Hours and dates

Hi guys,

 

I have two tables.

 

First table: Table with this columns about some calls: date and hour [dd:mm:YY hh:mm:ss], travel code, client, call duration [hh:mm:ss]

Second table: Table with some columns about the travels: travel code, start date travel [dd:mm:YY hh:mm:ss], end date travel [dd:mm:YY hh:mm:ss], start hours [hh:mm:ss], end hours [hh:mm:ss]

So I made a relationship between then, using travel code. Now I need to know the percent of call made during the travel using dax measure. Look this line for example:

 

First table: travel code: 250015; call started at 04/13 08:22:00; call ended 04/13 08:22:34
Second table: travel code: 250015; start travel 04/13 08:00; end travel 04/13 13:30

This travel should be counted. After count all of this cases, we should divide by the total of calls.

3 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    Not fully sure what you want. Can you share you expected result for further investigation?

    You can try formulas as below:

     

    diff1(seconds) = DATEDIFF(SUM(Table1[Call started]),SUM(Table1[Call ended]),SECOND)
    diff2(seconds) = DATEDIFF(SUM(Table2[Start travel]),SUM(Table2[End travel]),SECOND)
    Percentage = [diff1(seconds)]/[diff2(seconds)]

    If I have misunderstood, please feel free to let me know.

     

    Best Regards,
    Community Support Team _ Eason

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi， Anonymous 

    If you just want to get the percent of calls in the corresponding time period, please formulas like:

    Count1 = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Call started]>=SUM(Table2[Start travel])&&Table1[Call ended]<=SUM(Table1[Call ended])))
    All count = COUNT(Table1[Travel code])
    Result = [Count1]/[All count]

    Best Regards,
    Community Support Team _ Eason

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the answer,

       

      I think it's almost this, I tried this formulas, but it does not work. I'll give you another example:

       

       Call table:

       

      Call_Date_Hour Travel CodeClientCall duration
      14/04/2020 16:2418791668Cliente 100:00:36
      19/04/2020 07:1318805913Cliente 100:00:59
      19/04/2020 21:4818811065Cliente 100:00:36
      19/04/2020 22:2318811065Cliente 100:00:33
      19/04/2020 22:2518811065Cliente 100:00:31
      23/04/2020 10:3018824537Cliente 100:00:50
      23/04/2020 14:0618827367Cliente 100:00:30
      23/04/2020 14:2318827651Cliente 100:01:02
      23/04/2020 16:5218823981Cliente 100:00:43
      24/04/2020 11:3518828915Cliente 100:00:58
      24/04/2020 11:4118828915Cliente 100:00:44
      24/04/2020 12:1018828915Cliente 100:00:32
      24/04/2020 12:3618827367Cliente 100:00:32
      24/04/2020 19:1918834085Cliente 100:00:32
      24/04/2020 19:2818834085Cliente 100:00:30
      25/04/2020 08:3218834940Cliente 100:00:36
      25/04/2020 08:3318834940Cliente 100:00:33
      26/04/2020 11:2618834085Cliente 100:00:36
      08/05/2020 19:2618889171Cliente 989800:00:31
      08/05/2020 19:3018889171Cliente 989800:00:31
      09/05/2020 09:3518889171Cliente 989800:00:45
      09/05/2020 12:0618889171Cliente 989800:00:36

       

      Travels table:

       

      Travel codeTravel_Date_startTravel_Date_end
      187916682020-04-14 05:49:01.0002020-04-15 18:42:52.000
      188059132020-04-16 18:54:38.0002020-04-23 14:23:45.000
      188110652020-04-19 10:44:06.0002020-04-20 06:26:44.000
      188245372020-04-23 05:18:45.0002020-04-23 11:30:48.000
      188273672020-04-23 12:33:25.0002020-04-27 10:50:54.000
      188276512020-04-23 13:11:03.0002020-04-23 14:25:51.000
      188239812020-04-22 19:44:46.0002020-04-24 10:59:39.000
      188289152020-04-23 18:07:51.0002020-04-24 12:10:48.000
      188273672020-04-23 12:33:25.0002020-04-27 10:50:54.000
      188340852020-04-24 18:05:08.0002020-04-26 18:32:09.000
      188349402020-04-24 21:36:06.0002020-04-27 12:17:50.000
      188891712020-05-09 09:27:30.0002020-05-12 23:08:40.000

       

      We have to compare this two tables, travel by travel. For example: 

       

      14/04/2020 16:2418791668Cliente 100:00:36

      This call hapenned in the travel.

       

      We know that because if we look for this travel code on the travel table, we'll find:

      187916682020-04-14 05:49:01.0002020-04-15 18:42:52.000

       

      The call was at 14/04 16:24, after the travel begins (14/04 05:49) and before it ends (15/04 18:42). So I have to count it.

       

      Now, let's take another example:

       

      08/05/2020 19:2618889171Cliente 989800:00:31

       

      If we look for this travel code on the travel table, we'll find:

       

      188891712020-05-09 09:27:30.0002020-05-12 23:08:40.000

       

      The call was at 08/05 19:26, before the travel begins. In this case, I don't count this row on call table. If this call were after the travel ends, we don't need to call it too.

       

      So I have to count the cases like the first example (travel 18791668) and divide by all the call we had. Then, I'll find a percentage.

       

      The second example (travel 18889171) was just to show what we should not count.