Forum Discussion

Jos13's avatar
Jos13
Helper III
6 years ago
Solved

DATEDIFF

Hi Team,

I have the following data

I just wanted to count those records with status = "IN" and the time difference between the dates > 30 minutes for the same mobile number. In this example, the expected output is 2. One for 771234 and 1 from 6671. 

I was trying something like this:

Measure =
CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Status] = "IN",
DATEDIFF ( 'Table'[Date], EARLIER ( 'Table'[Date] ), MINUTE ) > 30
)

But that's wrong.

Please help me to solve this.

 

Thank you

Need Help
Tips and Tricks

11 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Jos13 , Create a column like

    time diff = datediff(maxx(filter(table, [mobile] =earlier([mobile]) && [status] ="Out" && [status] <>earlier([status]) && [Date] <earlier([Date])),[Date]),[Date], minute)

     This will time diff in minutes , you need to check for > 30

     

  • tex628's avatar
    tex628
    Community Champion
    6 years ago

    Did you miss a column in your picture? Theres only one date present

    /J

    • Jos13's avatar
      Jos13
      Helper III
      6 years ago

      Hi tex628 ,

       

      There is only one date column in the table. It indicates the date and time an issue came. I just wanted to count the issues only once that came from the same mobile number within 30 minutes. Issues with status "IN" are considered for the calculation.

       

      Thanks

      Jos

      • tex628's avatar
        tex628
        Community Champion
        6 years ago

        Alright, 

        So if theres 5 IN calls for the same number, are the 1's in the correct place?: 

        10:00 - 1

        10:15
        12:15 - 1
        12:30
        13:15 - 1

  • fhill's avatar
    fhill
    Resident Rockstar
    6 years ago

     

    I do thing in little steps, so this is just my style, and maybe someone can add to this to fix the potential In/In/out data bug?

     

    We need to define an 'OutTime' then you can DateDifff and go from there...

     

    OutTime = IF('Table'[Status] = "IN",
    CALCULATE(MIN('Table'[DateTime]), FILTER('Table', 'Table'[Mobile#] = EARLIER('Table'[Mobile#]) && 'Table'[Status] = "OUT" && 'Table'[DateTime] > EARLIER('Table'[DateTime]))))

     

    Once you have the OutTimes pulled into a new column, you can DateDiff the two columns (blanks in the OutTime will Blank the DateDiff)

    DateDiff = DATEDIFF('Table'[DateTime], 'Table'[OutTime], MINUTE)
     
    Then finally, just IF statement the count (which could easily be combined with the last step...
    Over30? = IF('Table'[DateDiff] > 30, 1)

     

     

     

     

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi Jos13 ,

     

     

    In this example, the expected output is 2. One for 771234 and 1 from 6671. 

     

    I don't quite understand your calculation logic. How do you get the result of 2? Which row is meet your requirements? Can you explain it in more detail for me?

     

     

    Best Regards,

    Icey