Group if Date/Time of one column within 24 hours of previous row in another column

[dmcguinn]

Hi All,

Apologies if covered elsewhere, I spent a couple days searching...

If Stop_Time is within 24 hours of next Start_Time I need the Sum of Production_Value for those rows as an additional column againest the Earliest Start_Time per grouping, could be more than 2 consectutive rows to be grouped in some instances (2 examples in Bold below).

Else I need the Production_Value for those (individual) records not resulting in a grouped result.

EXAMPLE DATA

Start_Time                         Stop_Time                         Production_Value

2/16/2021 3:20:01 PM	2/16/2021 11:00:00 PM	460
2/16/2021 11:20:00 PM	2/17/2021 7:00:01 AM	460
3/2/2021 3:20:01 PM	3/2/2021 11:00:00 PM	460
3/5/2021 8:50:01 PM	3/6/2021 6:00:00 AM	550
3/6/2021 8:00:00 AM	3/6/2021 11:30:01 AM	210
3/17/2021 11:50:01 PM	3/18/2021 7:00:01 AM	430
3/18/2021 7:50:01 AM	3/18/2021 11:50:01 AM	240
3/30/2021 7:50:01 AM	3/30/2021 3:00:00 PM	430
4/14/2021 11:50:01 PM	4/15/2021 6:10:00 AM	380
4/26/2021 11:50:01 PM	4/27/2021 7:00:01 AM	430
4/27/2021 7:50:01 AM	4/27/2021 10:30:00 AM	160
5/6/2021 11:50:01 PM	5/7/2021 7:00:01 AM	430
5/19/2021 11:50:01 PM	5/20/2021 7:00:01 AM	430
5/31/2021 11:50:01 PM	6/1/2021 7:00:01 AM	430
6/1/2021 7:45:01 AM	6/1/2021 10:40:00 AM	175
6/14/2021 11:50:01 PM	6/15/2021 7:00:01 AM	430
6/15/2021 7:30:00 AM	6/15/2021 9:10:00 AM	100
6/21/2021 11:50:01 PM	6/22/2021 7:00:01 AM	430
6/22/2021 7:50:01 AM	6/22/2021 9:35:01 AM	105
6/30/2021 11:50:01 PM	7/1/2021 6:25:00 AM	395
7/5/2021 11:50:01 PM	7/6/2021 7:00:01 AM	430
7/6/2021 7:50:01 AM	7/6/2021 12:05:00 PM	255
7/15/2021 11:30:01 PM	7/16/2021 4:50:01 AM	320
7/26/2021 11:20:00 PM	7/27/2021 2:00:00 AM	160
7/27/2021 4:20:01 AM	7/27/2021 7:00:01 AM	160
8/10/2021 11:50:01 PM	8/11/2021 7:00:01 AM	430
8/17/2021 11:50:01 PM	8/18/2021 7:00:01 AM	430
8/30/2021 11:50:01 PM	8/31/2021 7:00:01 AM	430
8/31/2021 7:50:01 AM	8/31/2021 3:00:00 PM	430
9/7/2021 11:50:01 PM	9/8/2021 4:50:01 AM	300
9/27/2021 11:50:01 PM	9/28/2021 6:20:01 AM	390
9/28/2021 7:50:01 AM	9/28/2021 11:00:00 AM	190
10/6/2021 11:50:01 PM	10/7/2021 4:30:00 AM	280
10/18/2021 11:50:01 PM	10/19/2021 5:00:00 AM	310
10/25/2021 8:20:00 PM	10/25/2021 11:00:00 PM	160
10/25/2021 11:50:01 PM	10/26/2021 7:00:01 AM	430
10/30/2021 8:20:00 AM	10/30/2021 1:30:00 PM	310
10/30/2021 3:30:00 PM	10/30/2021 5:30:01 PM	120
11/4/2021 9:40:01 PM	11/4/2021 11:00:00 PM	80
11/4/2021 11:30:01 PM	11/5/2021 5:40:00 AM	370
11/11/2021 11:50:01 PM	11/12/2021 7:00:01 AM	430
11/12/2021 8:20:00 AM	11/12/2021 11:10:01 AM	170
11/18/2021 11:50:01 PM	11/19/2021 7:00:01 AM	430
11/25/2021 11:50:01 PM	11/26/2021 6:20:01 AM	390
12/1/2021 8:25:00 PM	12/1/2021 11:00:00 PM	155
12/1/2021 11:50:01 PM	12/2/2021 4:20:01 AM	270
12/8/2021 11:50:01 PM	12/9/2021 7:00:01 AM	430
12/15/2021 11:50:01 PM	12/16/2021 7:00:01 AM	430
12/22/2021 2:30:01 AM	12/22/2021 7:00:01 AM	270
12/22/2021 7:50:01 AM	12/22/2021 8:30:01 AM	40
1/21/2022 12:20:01 AM	1/21/2022 3:30:00 AM	190
1/21/2022 8:20:00 AM	1/21/2022 11:00:00 AM	160
1/26/2022 11:50:01 PM	1/27/2022 7:00:01 AM	430
1/27/2022 7:50:01 AM	1/27/2022 3:00:00 PM	430
1/27/2022 3:50:00 PM	1/27/2022 10:40:00 PM	410
2/3/2022 11:50:01 PM	2/4/2022 4:30:00 AM	280
2/10/2022 8:35:01 PM	2/10/2022 11:00:00 PM	145
2/10/2022 11:30:01 PM	2/11/2022 1:20:01 AM	110
2/11/2022 3:40:01 AM	2/11/2022 5:10:01 AM	90
2/17/2022 11:50:01 PM	2/18/2022 7:00:01 AM	430
2/26/2022 8:20:00 AM	2/26/2022 5:30:01 PM	550
3/4/2022 8:50:01 PM	3/5/2022 7:00:01 AM	610
3/10/2022 11:50:01 PM	3/11/2022 7:00:01 AM	430
3/11/2022 7:30:00 AM	3/11/2022 8:40:00 AM	70
3/21/2022 11:50:01 PM	3/22/2022 7:00:01 AM	430
3/26/2022 9:10:00 PM	3/27/2022 6:00:00 AM	530
4/4/2022 11:50:01 PM	4/5/2022 7:00:01 AM	430
4/5/2022 7:30:00 AM	4/5/2022 12:10:00 PM	280

Required Result (Manually created from example data)

Appreciate any suggestions

[dmcguinn]

Thank you so much @v-yalanwu-msft, the solution is perfect!

[v-yalanwu-msft · Accepted solution]

Hi, @dmcguinn ;

You could create two column as group coulmn.

Column = 
var _pirstop=CALCULATE(MAX([Stop_Time]),FILTER(ALL('Table'),[Start_Time]<EARLIER([Start_Time])))
return IF([Start_Time]=MIN([Start_Time]),1, IF( DATEDIFF(_pirstop,[Start_Time],HOUR)>24,1))

Group = CALCULATE(SUM([Column]),FILTER('Table',[Start_Time]<=EARLIER([Start_Time])))

Then create a measure.

Value = 
CALCULATE(SUM([Production_Value]),FILTER(ALL('Table'),[Group]=MAX([Group])))

Then apply the column is not blank.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.