How to match activity times against a schedule in Power Query

[sellersmd]

Hello,

I have two tables I am working with.

One is a daily 'schedule' of times when certain activities should occur and includes the activity name, start time, and end time. In this table, activities are repeated with multiple start and end times, and start and end times can be re-used across activities. This table represents all possible times that activities can occur.

Activity	Start	End
A	12:00:00 AM	1:00:00 AM
A	2:30:00 AM	3:30:00 AM
A	6:00:00 PM	7:00:00 PM
B	12:00:00 AM	1:00:00 AM
B	6:00:00 PM	7:00:00 PM
C	2:30:00 AM	3:30:00 AM
C	6:00:00 PM	7:00:00 PM

The second table is a list of activities along with date and what time the activity started. Activities are started multiple times per day:

Activity	Date	Start Time
B	01-01-23	12:27:00 AM
C	01-01-23	3:44:00 AM
A	01-01-23	12:56:00 AM
B	01-02-23	6:03:00 PM
C	01-02-23	6:15:00 PM
A	01-03-23	3:15:00 AM

Ultimately, I'd like to create a new table that summarizes the information as follows:

Date	Activity	Total Count	Count (On Time)	Count (Early)	Count (Late)
1-1-23	A
1-1-23	B
1-1-23	C
1-2-23	A
1-2-23	B
1-2-23	C
1-3-23	A
1-3-23	B
1-3-23	C

The Count (On Time) would represent the daily count of activities that started between the start and end time, the Count (Early) would represent the daily count of activities that started up to 30 minutes prior to the start time, and the Count (Late) would represent the daily count of activities that started up to 30 minutes after the end time.

I can easily create the new table with Date, Activity, and Total Count using SUMMARIZE(). I am struggling to create new columns for Count (On Time), Count (Early), and Count(Late).

Any help with the appropriate DAX to get these figured would be greatly appreciated!

Thanks in advance.

Matt

[Anonymous · Accepted solution]

Hi @sellersmd ,

I think the first table is the schedule table, and the second table is the table of the actual start time, right?

Here's my solultion. It's recommended to complete it in Power Query.

1.You can merge the Actual table with the Schedule table. Reference: Merge Queries

2.Expand the tables in the Schedule column.

3.Create a custom column to get the On Time, Early and Late. Refernce: Add a custom column - Power Query | Microsoft Learn

4.Remove the rows contains null.

Click OK.

5.Remove the unneeded columns and add a custom column with 1.

6.Pivot the Custom column to get the countings.

Pivot columns (Power Query) - Microsoft Support

Best Regards,

Stephen Tao

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

[sellersmd]

Hi Stephen,

Elegant solution and easy to follow. I was unaware that when you merged queries you could do a many-to-one match - that was exactly what I needed! Thanks for your help. 

[KeyurPatel14]

Hii @sellersmd ,
Can you please provide full output so that I can help you quicker??

[sellersmd]

Hello @KeyurPatel14!

Thank you for taking the time to reply. Here are dropbox links to a test schedule and test activity data. I hope thats what you were looking for by full output. If not, please clarify and I will provide whatever is needed!

Test Schedule 

Test Activity Data 

Thank you in advance!