How to count all events happening 24hrs from an event?

[Gerbil]

I have a list of events with their begin and end times like so:

Ref Num	Start	End
114	1/1/2023 0:05	1/1/2023 6:29
891	1/1/2023 19:06	1/1/2023  19:53
416	1/1/2023 22:06	1/1/2023  22:46
310	1/1/2023 23:10	1/1/2023 23:02
200	1/2/2023 0:14	1/2/2023 3:07
926	1/2/2023 3:19	1/2/2023 3:36
398	1/2/2023 6:19	1/2/2023 6:41
608	1/3/2023 10:25	1/3/2023 10:18
40	1/3/2023 13:27	1/3/2023 13:08
403	1/3/2023  16:33	1/3/2023 16:35
668	1/3/2023 20:09	1/3/2023 20:15
854	1/4/2023 1:09	1/4/2023 2:01
250	1/4/2023 5:17	1/4/2023 5:12

I want to count all the events that are within 24hrs after the event time like so:

Ref Num	Start	End	Count
114	1/1/2023 0:05	1/1/2023 6:29	4
891	1/1/2023 19:06	1/1/2023  19:53	6
416	1/1/2023 22:06	1/1/2023  22:46	5
310	1/1/2023 23:10	1/1/2023 23:02	4
200	1/2/2023 0:14	1/2/2023 3:07	3
926	1/2/2023 3:19	1/2/2023 3:36	2
398	1/2/2023 6:19	1/2/2023 6:41	1
608	1/3/2023 10:25	1/3/2023 10:18	5
40	1/3/2023 13:27	1/3/2023 13:08	4
403	1/3/2023  16:33	1/3/2023 16:35	3
668	1/3/2023 20:09	1/3/2023 20:15	3
854	1/4/2023 1:09	1/4/2023 2:01	2
250	1/4/2023 5:17	1/4/2023 5:12	1

I was able to do this in excel using this function:

=SUMPRODUCT(--($E$6:$E$18<(E6+1)),--($E$6:$E$18>E6))+1

where E is the Start column. But I cannot figure how to do this in PowerBI.

[v-zhangti · Accepted solution]

Hi, @Gerbil 

You can try the following methods.
Column:

Start+24h = [Start]+1

Count = 
CALCULATE ( COUNT ( 'Table'[Ref Num] ),
    FILTER ( ALL ( 'Table' ),
        [Start] >= EARLIER ( 'Table'[Start] )
            && [Start] <= EARLIER ( 'Table'[Start+24h] ) ) )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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