• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to compare time? - actual time in between 2 predefined timeslot|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to compare time? - actual time in between 2 predefined timeslot|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to compare time? - actual time …
sp_PrintTopic sp_TopicIcon
How to compare time? - actual time in between 2 predefined timeslot
Avatar
michael wong

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 13, 2021
sp_UserOfflineSmall Offline
1
March 13, 2021 - 10:58 pm
sp_Permalink sp_Print

Hi guys,

I am new to Power BI and I hope to do a support dashboard on whether a robot job ended according to the scheduled time slot.

Scheduled Job Timings:
Job|start_time|end_time
ABC|12:00 am|1:00 am
EDF|1:00 am|2:00 am
GHI|2:00 am|3:00 am
.......

 

compliance table:
Job|end_time|Is_Compliance
ABC|20-Feb-17 07:02 pm|N
EDF|5/10/2016 1:30:00 AM|Y
GHI|12-Dec-16 03:59 am|
JKL|10-Feb-17 04:38 am|
MNO|06-Feb-17 10:00 am|
PQR|07-Nov-16 03:25 am|
STU|20/1/2017 6:45|
VW|08-Dec-16 02:52 pm|
XYZ|16-Dec-16 04:19 pm|

how do I populate the Is_Compliance value? Attached is the powerbi file with the data above. Would really appreciate some guidance. tx.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 14, 2021 - 12:20 pm
sp_Permalink sp_Print sp_EditHistory

Hi Michael,

Welcome to our forum! There are some assumptions missing from the information provided. The Job Compliance table has dates and time in one column and the Scheduled Job Time table only contains times. In the attached file I've assumed it doesn't matter if the job start date is more than 1 day prior. 

You'll see in Power Query I've split the time out from the Date/Time end_time and then merged it with the Scheduled Job Timings table. This is an Excel file for simplicity, but the steps are the same in Power BI.

Mynda

Avatar
michael wong

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 13, 2021
sp_UserOfflineSmall Offline
3
March 14, 2021 - 8:55 pm
sp_Permalink sp_Print

Hey Mynda,

 

Thanks for the invite and making me feel welcome. You are right! The date plays an important role in the end_time column. I should have name end_time as job_time. As there are several entries in the job_compliance table for the same job, my intention was to get the latest job "ended" record for the current date and check compliancy. For eg, when I am running the report for today (14 Mar), job ABC has 2 "jobs" in the job_compliancy table. I only want the latest "ended" job (end_time=14/3/2021 7:02:00 PM, key=1234567). Then check the compliancy. The jobs MNO, PQR, and STU will not be counted / displayed in today's dashboard. 

I have attached the modified job_compliancy excel for your reference. I am learning on the job and am picking up PowerBI fundamentals 🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
March 15, 2021 - 1:11 pm
sp_Permalink sp_Print

Hi Michael,

Please see example file attached.

Note: I used this technique to Number Grouped Data in Power Query. And I added Table.Buffer to the Filtered Rows1 step to prevent Power Query losing the sort order.

Hope that helps.

Mynda

Avatar
michael wong

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
March 13, 2021
sp_UserOfflineSmall Offline
5
March 16, 2021 - 12:53 am
sp_Permalink sp_Print

hi Mynda,

It really helps. Just found out about another scenario though. Some of the jobs are schedule to also run on other parts of the day, shown in red as follows,

How do we cater to such scenario? tx.

Job start_time end_time
ABC 12:00 AM 1:00 AM
EDF 1:00 AM 2:00 AM
GHI 2:00 AM 3:00 AM
JKL 3:00 AM 4:00 AM
MNO 4:00 AM 5:00 AM
PQR 5:00 AM 6:00 AM
STU 6:00 AM 7:00 AM
VW 7:00 AM 8:00 AM
XYZ 8:00 AM 9:00 AM
ABC 9:00 AM 10:00 AM
JKL 10:00 AM 11:00 AM
PQR 11:00 AM 12:00 PM
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4448
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 16, 2021 - 4:27 pm
sp_Permalink sp_Print

I don't know. You need to tell me what the rules/logic would be.

Also, the examples above don't correlate to any of the 'execution ended' times in your original data, so right now they would simply be ignored. Perhaps you can provide a sample file that contains all scenarios and the desired result. Please just provide one file with one sheet containing the two tables and example of your desired result so I can see it all in one place and don't have to keep recreating a file from what you send.

Thanks,

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Velouria, Riny van Eekelen, Alexandra Radu, michael serna
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6216
Posts: 27250

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.