Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |