Forum Discussion
DATEDIFF
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
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
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".
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
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.