Forum Discussion

pardeepd84's avatar
pardeepd84
Icon for Helper III rankHelper III
5 years ago
Solved

Countif and Sumif to calculate difference

Hi,

 

I have a large dataset which contains Client ID, employee ID, planned visit, planned start time, actual start date, actual start time. I need to count the number of times 2 employees visit the same visit client on the same day and arrive within 5 minutes of each other.  

 

In the example below employee ID 25 and 26 arrived within 5 minutes of each other (employee ID 25 arrived at 14:33 on 10/02/2021 and employee ID 26 arrived at 14:35 on 10/02/2021) on the same day (10/02/2021) to see client ID 123.  This is then recorded as "ok", if they didn't I need it to record "not ok".

 

Please see sample data below, column AH is what I want the query to return. 

 

Need Help
Tutorial Requests
  • pardeepd84's avatar
    pardeepd84
    5 years ago

    I have managed to resolve this using another DAX formula, thank you for your assistance with this query though. 

8 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    pardeepd84 , Please check if this new column can help

     

    New column =

    var _1= countx(filter(Table, [Client ID] =earlier([Client ID]) && [EMP ID] <> earlier([EMP ID]) && [Actual Date] <> earlier([Actual Date]) &&
    [Actual Start Date] >=earlier([Actual Start Date]) -time(5,0,0) && [Actual Start Date] <= earlier([Actual Start Date]) +time(5,0,0)),[Client ID])+0

     

    return

    if(_1  >0, "Not OK", "OK")

    • pardeepd84's avatar
      pardeepd84
      Icon for Helper III rankHelper III
      5 years ago

      amitchandak 

       

      I have managed to get the formula to work however I don't think it is counting correctly.  

       

      I need the formula to count the client ID and if they have 2 visits scheduled at the same time on the same day then return me the result if 2 employees arrived within 5 minutes of each other i.e

       

      In the sample data Client ID 1234 had 2 planned visits on 10/02/2021 at 08:30, Jo and Jenny arrived within 5 minutes of each other and so it records it as "ok".  However for Client ID 6789 James and Emma did not arrive within 5 minutes of each other and so it records it as "Not Ok". 

       

       

    • pardeepd84's avatar
      pardeepd84
      Icon for Helper III rankHelper III
      5 years ago

      Hi amitchandak ,

       

      I need a calculation as follows: 

       

      • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employee attend within 5 minutes of each other then return the result of “Ok"

      and 

       

      • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employees but do not arrive within 5 minutes of each other then return the result of “Not Ok”.

      I have tried your suggestion however it does not return the correct results.

       

      In your example it states time(5,0,0) - would this not be calculating 5 hours rather than 5 minutes?  Please correct me if I am wrong. 

       

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi pardeepd84 ,

     

    According to your needs, I did the following tests and got the correct expected results:

    Col =
VAR cur_index = 'Table'[Index]
VAR next_time =
    CALCULATE (
        MAX ( 'Table'[Time] ),
        FILTER (
            'Table',
            'Table'[Index] = cur_index + 1
                && 'Table'[Client ID] = EARLIER ( 'Table'[Client ID] )
        )
    )
RETURN
    next_time
    M = 
IF (
    MAX ( 'Table'[Col] ) = BLANK (),
    BLANK (),
    IF ( ABS ( MAX ( 'Table'[Col] ) - MAX ( 'Table'[Time] ) <= 5 ), "OK", "Not OK" )
)

    Below is the sample pbix file.


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

     

    Best Regards,
    Henry

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • pardeepd84's avatar
      pardeepd84
      Icon for Helper III rankHelper III
      5 years ago

      I have tried the above however it doesn’t return the results that I require, please see further explanation to the query:

       

      • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employee attend within 5 minutes of each other then return the result of “Ok”
      • Where the client has 2 visits planned at the same time on the same day (i.e 08/02/2021 at 08:15) and 2 employees but do not arrive within 5 minutes of each other then return the result of “Not Ok”

      In the same data below:

       

      Client ID 1234 had 2 visits planned on 08/02/2021 at 08:15 and both employees (Jenny and Jo) arrived within 5 minutes of each other – Jenny arrived at 07:43:13 and Jo arrived at 07:43:38.

       

      Client ID 5678 had 2 visits planned on 08/02/2021 at 19:05 but the employees (Fred and Ned) did not arrive within 5 minutes of each other – Fred arrived at 19:01:53 and Ned arrived at 19:17:29. Ned arrived 15 minutes later than Fred, therefore this would be recorded as "Not Ok". 

       

    • pardeepd84's avatar
      pardeepd84
      Icon for Helper III rankHelper III
      5 years ago

      Hi v-henryk-mstf 

       

      I have tried this with my own data and also changed the time in per your example however it does not produce the same results.  Please see screenshot below:

       

       

      I would have expected to see for Client 1234 in Col the result of 17 however it appears as 13, the same is for client ID 5678 - it returns the answer as 35 not 56 as expected.  

       

      I have followed your example:

       

      Col =
      VAR cur_index = 'Table'[Index]
      VAR next_time =
      CALCULATE (
      MAX ( 'Table'[Time] ),
      FILTER (
      'Table',
      'Table'[Index] = cur_index + 1
      && 'Table'[Client ID] = EARLIER ( 'Table'[Client ID] )
      )
      )
      RETURN
      next_time
       
      M =
      IF (
      MAX ( 'Table'[Col] ) = BLANK (),
      BLANK (),
      IF ( ABS ( MAX ( 'Table'[Col] ) - MAX ( 'Table'[Time] ) <= 5 ), "OK", "Not OK" )
      )
       
      This does not produce the expected results, please could you advise if I am doing something wrong here.
  • pardeepd84's avatar
    pardeepd84
    Icon for Helper III rankHelper III
    5 years ago

    I have managed to resolve this using another DAX formula, thank you for your assistance with this query though.