DATEDIFF

[Jos13]

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

[Icey]

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

[tex628 · Accepted solution]

@Jos13 , 

I think this works, see the attached file.

/ J

[fhill]

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)

[amitchandak]

@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]

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

/J

[Jos13]

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]

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

[Jos13]

Hi @tex628 ,

Yes they are.

[tex628]

In that case i would advice you to start with creating a categorizing column, converting each datetime to whole and half hour. 

Here I'm rounding up to the nearest whole or half hour. 

2019-01-01 09:49   ->   2019-01-01 10:00
2019-01-01 10:19   ->   2019-01-01 10:30
2019-01-01 10:31   ->   2019-01-01 11:00
2019-01-01 10:54   ->   2019-01-01 11:00

This column you will then be able to use as a grouping column. In other words when you calculate the number of calls you will group the phone number together with this column to create exactly how many intervals are present for the specific caller. 

The measure will look something along the lines of:

Measure = COUNTROWS(SUMMARIZE(FILTER(Table , Table[Status] = "IN"), Table[Mobile] , Table[Calculated Column]))

But it will be easier to work out once the column is created. 

/ J

[tex628]

Alright, just realized that this will not meet your requirement as the 09:49 and 10:10 will be counted as two in my scenario, which it shouldn't. 

I need to think a bit regarding this one.

[Jos13]

The gap between the calls from the same mobile number should be greater than 30 minutes in order to count it once. And the status should also be "IN".