Inquiry on comparing time

micgiggs · Mar 12, 2021

AlanSidman · Mar 12, 2021

Is it safe to assume that all jobs start and ended on the same day. Your first table does not take into consideration the date which is in the second table.

Chihiro · Mar 12, 2021

It looks like all duration is 9 hours. If that's the case, you can simply use custom column in 2nd table. But I suspect duration may change based on Job.

Now, do you want to do this in DAX measure or at query stage using M code?

Without knowing full scope, my guess is that you wish this to be done for each row context of 2nd table (i.e. Calculated Column).

For that, I'd recommend doing it in query stage.

Ex:
Merge 1st table into 2nd table using Job as Key. Expand start_time & end_time (I added prefix "o").
Use following to add time to date.

Code:

DateTime.From(Text.From(Date.From([end_time])) & " " & Text.From([o.start_time]))

Do the same for o.end_time. However, since it could cross over midnight into next day...

Code:

if [o.end_time] < [o.start_time] then DateTime.From(Text.From(Date.AddDays(Date.From([end_time]),1)) & " " & Text.From([o.end_time])) else DateTime.From(Text.From(Date.From([end_time])) & " " &  Text.From([o.end_time]))

Then you just need to check that [end_date] falls between two custom columns.

Code:

if [Custom.1]<> null and List.Median({[end_time],[Custom],[Custom.1]}) = [end_time] then "yes" else "no"

Now, you could simplify this into single custom function. But it's kept as individual steps to make it easier to follow logic flow.

micgiggs · Mar 16, 2021

AlanSidman said:
Is it safe to assume that all jobs start and ended on the same day. Your first table does not take into consideration the date which is in the second table.

Hi Alan,
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=16/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 neglected to add 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.

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
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

compliance table:
Job|end_time|Message|Key|is_compliance
ABC|3/16/2021 19:02|ABC execution ended|1234567|N
EDF|3/16/2021 1:30|EDF execution ended|89101112|Y
GHI|3/16/2021 3:59|GHI execution ended|10111213|
ABC|3/16/2021 19:00|ABC execution started|1234567|
EDF|16/03/2021 1:25|EDF execution ended|30405060|
GHI|16/03/2021 3:55|GHI execution ended|98765432|
JKL|3/16/2021 4:38|JKL execution ended|5555555|
MNO|3/16/2021 4:02|MNO execution ended|6666666|
PQR|3/13/2021 3:25|PQR execution ended|777777|
STU|3/12/2021 6:45|STU execution ended|8888888|
VW|3/16/2021 14:52|VW execution ended|9999999|
XYZ|3/16/2021 16:19|XYZ execution ended|11111111|
JKL|3/16/2021 4:30|JKL execution started|5555555|
ABC|16/03/2021 18:02|ABC execution ended|2234567|
ABC|16/03/2021 18:00|ABC execution started|2234567|
EDF|16/03/2021 1:20|EDF execution started|30405060|
GHI|16/03/2021 3:50|GHI execution started|98765432|
XYZ|16/03/2021 16:19|XYZ execution ended|100000001|

Inquiry on comparing time

micgiggs

New Member

AlanSidman

Well-Known Member

Chihiro

Excel Ninja

micgiggs

New Member