Create a dax to get count of events occurred in specific time

[amikm]

I have a data like like

event_time_utc

12/18/2021 8:50:07 PM

12/18/2021 8:51:47 PM

12/18/2021 8:53:10 PM

12/18/2021 8:53:30 PM

12/18/2021 8:54:10 PM

12/18/2021 8:56:47 PM

I am trying to create metrics to display No. of events that occurred in 1.5 min.
So for example, I have a Bus that is GPS enabled and I am getting one entry in my table whenever there is a break applied in Bus or Bus took left or right direction means any movement. 

If you see data, the bus starts on 12/18/2021 at 8:50:07 PM, so let's take event count as 1, and we keep comparing the event time interval with the next event and will see If the next event is within 1.5 min or more than that. As per data our second event was triggered at 12/18/2021 8:51:47 PM and If we see the difference it is 1.66 which is greater than 1.5, so now our event count will become 2, similarly see the next event is 12/18/2021 8:53:10 PM and if we see the difference it is 2.61 so still the count will become 3, now see the next event 12/18/2021 8:53:30 PM and if we see the difference it is 0.33 which is less than 1.5 so the count will be 3 only. Now let's see the next event 12/18/2021 8:54:10 PM, If we see the difference it is 1.33 which is less than 1.5 so the count will be still 3, next event 12/18/2021 8:56:47 PM, If we see the difference it is 2.61 which is greater than 1.5  so our count will increase to 4 and logic continues like that only

in short, If we are taking the difference between two events and If the difference between consecutive events is less than 1.5 Min then we will consider all those events as one bucket and once we see the difference between events is greater than 1.5 then the count will increase by 1 and in this way we keep calculating the count of events.

So, In the above example, I am expecting my value as 4 that I want to show in my card visual.

At high level:
event_time_utc               Eventdiff  Cnt1.5 Min

12/18/2021 8:50:07 PM	0	1 (start)
12/18/2021 8:51:47 PM	1.66	2
12/18/2021 8:53:10 PM	2.61	3
12/18/2021 8:53:30 PM	0.33	3
12/18/2021 8:54:10 PM	1.33	3
12/18/2021 8:56:47 PM	2.61	4

[ValtteriN]

Hi,

This should do what you want:

Start data:

Calculated column (I use index to calculate lastvalue per row and determine second difference): 

Time diff 90 interval C =

var index = Eventtime[Index]

var lastvalue = CALCULATE(MAX(Eventtime[event_time_utc]),all(Eventtime),Eventtime[Index]=index-1)

var timediff = DATEDIFF(lastvalue,Eventtime[event_time_utc],SECOND)

var basecalc = if(or(timediff>90,Eventtime[Index]=0),1,0)

return

basecalc

Measure (calculate running total based on previous if column):

Time diff 90 interval =

var maxindex = MAX(Eventtime[Index]) return

calculate(SUM(Eventtime[Time diff 90 interval C]),Eventtime[Index]<=maxindex,ALL(Eventtime))

End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

[amikm]

Thanks, @ValtteriN, But I have an issue with a column that is part of this table. Say the column name is device and when I am using the column as a slicer, I am getting wrong data as the value of the index is changed and hence the difference will also change, and the count of the event will change too.


You can think data like this

event_time_utc    devicename

12/18/2021 8:50:07 PM	A
12/18/2021 8:51:47 PM	A
12/18/2021 8:53:10 PM	B
12/18/2021 8:53:30 PM	B
12/18/2021 8:54:10 PM	B
12/18/2021 8:56:47 PM	C

The data is working fine, If I doesn't have any filter for any device

[amikm]

Thanks, @ValtteriN, Can I also get the difference between the 1st event (1st row) and the difference of the 2nd event( 2nd row) in a calculated column? It might help me debug the entire dataset. 

Also, I am looking for a time slider in my visual where I can filter my sensor data for 00:00 23:59 

[ValtteriN]

Hi,

For a column containing timediff you can just place the formula in the var "timediff" in a column. That will give you the second difference ( DATEDIFF(lastvalue,Eventtime[event_time_utc],SECOND)). For a time slicer you create a calculated column with time data type and this kind of formula: 

Time diff 90 interval C =

FORMAT(Eventtime[event_time_utc],"hh:mm:ss") and then use it as a slicer.

[amikm]

The data type for this column would be text instead of date as a result, I can't use this column as a time range slider.

Also, I am checking the below Dax

Calculated column (I use index to calculate lastvalue per row and determine second difference): 

Time diff 90 interval C =

var index = Eventtime[Index]

var lastvalue = CALCULATE(MAX(Eventtime[event_time_utc]),all(Eventtime),Eventtime[Index]=index-1)

var timediff = DATEDIFF(lastvalue,Eventtime[event_time_utc],SECOND)

var basecalc = if(or(timediff>90,Eventtime[Index]=0),1,0)

return

basecalc

Measure (calculate running total based on previous if column):

Time diff 90 interval =

var maxindex = MAX(Eventtime[Index]) return

calculate(SUM(Eventtime[Time diff 90 interval C]),Eventtime[Index]<=maxindex,ALL(Eventtime))

When I am checking it manually for the attached dataset, I am getting more than 38 events for events that occurred on 18/12/2021 (MM-DD-YYYY)


This is giving me 38 whereas when I am calculating the events occurred within 1.5 min (90 sec), I am getting around 44.
event_time_utc;Time diff 90 interval C

4:33:26 PM	1
4:58:36 PM	1
4:59:56 PM	0
5:00:16 PM	0
5:11:26 PM	1
5:12:06 PM	0
5:12:26 PM	0
5:24:16 PM	1
5:24:36 PM	0
5:38:46 PM	0
5:49:16 PM	1
5:59:26 PM	1
5:59:56 PM	0
6:08:26 PM	1
6:13:46 PM	0
6:23:36 PM	1
6:25:46 PM	0
6:31:16 PM	1
6:31:36 PM	0
6:35:56 PM	1
6:37:06 PM	0
6:37:26 PM	0
6:37:56 PM	0
6:38:16 PM	0
7:06:07 PM	1
7:06:27 PM	0
7:07:27 PM	0
7:13:17 PM	1
7:19:27 PM	1
7:20:37 PM	0
7:32:07 PM	1
7:32:37 PM	0
7:41:47 PM	1
7:49:17 PM	1
7:51:07 PM	1
7:51:37 PM	0
8:01:47 PM	1
8:02:17 PM	0
8:13:37 PM	1
8:15:37 PM	1
8:16:07 PM	0
8:27:47 PM	1
8:28:07 PM	0
8:29:37 PM	0
8:36:37 PM	1
8:36:57 PM	0
8:46:07 PM	1
8:50:07 PM	1
8:51:47 PM	1
8:56:47 PM	1
8:57:57 PM	0
9:07:07 PM	1
9:07:47 PM	0
9:08:07 PM	0
9:08:37 PM	0
9:16:37 PM	1
9:17:17 PM	0
9:24:27 PM	1
9:24:47 PM	0
9:34:47 PM	1
9:35:07 PM	0
9:42:47 PM	1
9:45:37 PM	1
9:49:47 PM	1
9:50:07 PM	0
9:57:27 PM	0
9:58:37 PM	0
10:33:47 PM	1
10:34:07 PM	0
10:34:27 PM	0
10:34:57 PM	0
10:43:37 PM	1
11:01:26 PM	1
11:02:06 PM	0
11:03:06 PM	0
11:03:56 PM	0
11:04:26 PM	0
11:25:26 PM	1
11:25:56 PM	0
11:27:16 PM	0
11:33:36 PM	0
11:34:56 PM	0
11:44:56 PM	0
11:48:06 PM	1
11:48:26 PM	0

I can some issues near to 5:24:36 PM and 5:38:46 PM