Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jcastr02
Post Prodigy
Post Prodigy

groups of exceeding thresholds that are consecutive

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. 

Store8:00:00 AM8:30:00 AM9:00:00 AM9:30:00 AM10:00:00 AM10:30:00 AM11:00:00 AM11:30:00 AM12:00:00 PM12:30:00 PM1:00:00 PM1:30:00 PM2:00:00 PM2:30:00 PM3:00:00 PM3:30:00 PM4:00:00 PM4:30:00 PM5:00:00 PM5:30:00 PM6:00:00 PM6:30:00 PM7:00:00 PM7:30:00 PM8:00:00 PM8:30:00 PM9:00:00 PM9:30:00 PM10:00:00 PM10:30:00 PM11:00:00 PM11:30:00 PMThresholdDesired Result
37217.59.89.410.29.99.79.68.28.49.49.08.98.97.88.27.97.26.37.27.55.14.13.93.53.66.06.16.22.01.71.41.25.78:00:00AM-5:30:00PM;     8:30:00PM-9:30:00PM
60719.412.310.512.110.611.511.19.09.28.510.09.89.59.39.38.08.06.86.36.04.33.83.12.72.22.16.810.01.81.31.81.26.18:00:00AM-5:00:00PM;     9:00:00PM-9:30:00PM
717718.522.922.621.219.919.418.716.416.218.317.015.914.315.214.914.213.811.911.511.16.24.74.63.810.03.13.42.72.32.21.61.49.98:00:00AM-5:30:00PM;     8:00:00PM
1549513.515.514.614.012.713.411.210.89.66.88.79.89.69.28.37.98.56.55.14.62.62.40.7         9.68:00:00AM-12:00:00PM;     1:30:00PM-2:00:00PM
123005.56.110.712.612.612.110.410.311.310.89.69.98.99.810.39.78.68.97.87.16.76.63.53.43.22.32.31.71.41.51.61.46.49:00:00AM-6:30:00PM

 

1 REPLY 1
amustafa
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.