groups of exceeding thresholds that are consecutive

[jcastr02]

Hello I have a list of stores with their avg. call volume per interval and a respective threshold for that store.  I'm trying to pull consecutive intervals that are at or above the threshold.  For Example - Store 3721 the intervals that were over the threshold of 5.7 was during the intervals of 8:00:00AM-5:30:00PM and    8:30:00PM-9:30:00PM.  Some stores may only have one length of consecutive intervals others may have numerous.  Please see desired result below. 

Store	8:00:00 AM	8:30:00 AM	9:00:00 AM	9:30:00 AM	10:00:00 AM	10:30:00 AM	11:00:00 AM	11:30:00 AM	12:00:00 PM	12:30:00 PM	1:00:00 PM	1:30:00 PM	2:00:00 PM	2:30:00 PM	3:00:00 PM	3:30:00 PM	4:00:00 PM	4:30:00 PM	5:00:00 PM	5:30:00 PM	6:00:00 PM	6:30:00 PM	7:00:00 PM	7:30:00 PM	8:00:00 PM	8:30:00 PM	9:00:00 PM	9:30:00 PM	10:00:00 PM	10:30:00 PM	11:00:00 PM	11:30:00 PM	Threshold	Desired Result
3721	7.5	9.8	9.4	10.2	9.9	9.7	9.6	8.2	8.4	9.4	9.0	8.9	8.9	7.8	8.2	7.9	7.2	6.3	7.2	7.5	5.1	4.1	3.9	3.5	3.6	6.0	6.1	6.2	2.0	1.7	1.4	1.2	5.7	8:00:00AM-5:30:00PM;     8:30:00PM-9:30:00PM
6071	9.4	12.3	10.5	12.1	10.6	11.5	11.1	9.0	9.2	8.5	10.0	9.8	9.5	9.3	9.3	8.0	8.0	6.8	6.3	6.0	4.3	3.8	3.1	2.7	2.2	2.1	6.8	10.0	1.8	1.3	1.8	1.2	6.1	8:00:00AM-5:00:00PM;     9:00:00PM-9:30:00PM
7177	18.5	22.9	22.6	21.2	19.9	19.4	18.7	16.4	16.2	18.3	17.0	15.9	14.3	15.2	14.9	14.2	13.8	11.9	11.5	11.1	6.2	4.7	4.6	3.8	10.0	3.1	3.4	2.7	2.3	2.2	1.6	1.4	9.9	8:00:00AM-5:30:00PM;     8:00:00PM
15495	13.5	15.5	14.6	14.0	12.7	13.4	11.2	10.8	9.6	6.8	8.7	9.8	9.6	9.2	8.3	7.9	8.5	6.5	5.1	4.6	2.6	2.4	0.7										9.6	8:00:00AM-12:00:00PM;     1:30:00PM-2:00:00PM
12300	5.5	6.1	10.7	12.6	12.6	12.1	10.4	10.3	11.3	10.8	9.6	9.9	8.9	9.8	10.3	9.7	8.6	8.9	7.8	7.1	6.7	6.6	3.5	3.4	3.2	2.3	2.3	1.7	1.4	1.5	1.6	1.4	6.4	9:00:00AM-6:30:00PM

[amustafa]

It took some work to get your desired results. The sample data you provided needed to be un-pivot first to get sequential rows. I have also added a Date column because you may need to do this for different dated as well. First I added a Group ID column in Power Query because this cannot be done in DAX to identify groups of consecutive start/end times for each store id and date/time. Once that is done, I created a DAX column "TimePeriodsConcatenated".

See my work files in shared drive.

Consecutive Time Detection

If I answered your question, please mark this thread as accepted and Thums Up!
Follow me on LinkedIn:
https://www.linkedin.com/in/mustafa-ali-70133451/