How to measure time duration in seconds using previous entry

DavidNealon · June 4, 2018, 9:59pm

Hi,

I’m building a Diary to determine when someone is working or resting and want to add the time in seconds between entries. There are multiple drivers in the same column

Sample Entries

Date	Driver	Work Rest	Work Rest State
08/05/2017 10:00:00am	Brian	Work	1
08/05/2017 10:01:00am	Brian	Rest	2
08/05/2017 10:30:00am	Evan	Work	1
08/05/2017 10:31:00am	Bill	Work	1
08/05/2017 10:32:00am	Evan	Rest	2
08/05/2017 10:40:00am	Bill	Rest	2

I’d like to calculate Total Seconds Working and Total Seconds Resting as a starting point.

I figure the easiest way would be to look for the date of the persons previous entry and bring the date into the current row and calculate the time difference between the two dates.

I’m not sure how to bring the previous date into the current row. I hope you can help as this is my first post asking as a question.

sam.mckay · June 5, 2018, 3:36am

One thing I would recommend is in the query editor to break out the times from the date column.

To do this you would duplicate the date column and then format it to time.

This way you can more easily calculated on time related data and then also have a column which is just the date and can be connected to you date table via a relationship.

Out of the current columns that you have what are the one which determine the start and end time, to then calculate the totals of working and resting.

To me it’s not so clear at the moment.

Like does this represent that Brian has worked for 1 minute??

DavidNealon · June 5, 2018, 3:48am

Hi Sam,

I was thinking of breaking out the Date and Time seperately but I’m concerned about how I’d measure between the two time stamps on different days.

You are correct both the start and end time at the moment are in the same column (Date) which is why I think I’ll need to bring the previous one down onto the same row to do the calculation.

That does show that Brian has only worked for 1 minute and is exactly the small amount of time calculations I’ll need to do as well as longer ones that go for several hours over several days for example when someone is not working over a weekend.

sam.mckay · June 5, 2018, 5:24am

This is an interesting one. I don’t quite have the answer yet but you want to actually flip the table somehow so that Work Dates and Rest Dates are in column by themselves.

I’ll have to try this out.

sam.mckay · June 5, 2018, 9:15am

Played around with a few ideas and this is the best one I’ve come up with.

This is the technique anyway.

I’ve re-created the table myself to test.

The first thing I did was duplicate the query to create another one exactly the same

I then filtered this just by Rest

Then filtered the original by Work

Then we have the two start and end date and time in different tables.

You could do a few things at this point, some may be better than what I’ve done so I’ll leave that up to you.

What I did do though was then use a calculated column to bring in the start time (or end time I’m confusing myself).

Like so…

So now you have the one table with dates aligned.

Hopefully this give you ideas around how to solve this one

DavidNealon · June 5, 2018, 11:21am

Awesome thanks Sam

DavidNealon · June 15, 2018, 6:31am

Hi Sam,

I tried what you mentioned but it hasn’t worked. Main reason being is because the same Driver logs on and off several times so there is more than one entry to lookup and the search engine gets confused.

Essentially I need to be able to bring down the previous time stamp of the same drivers entry and measure the time duration between the two entries.

Hope you can help

Regards

Dave

DavidNealon · June 15, 2018, 10:00am

Hi Sam,

Here’s a chance to show of the time intelligence.

Following on from the Work Diary entries once I am able to calculate duration of Work or Rest hours one of the requirements is to be able to calculate if in any period of 5 and half hours a driver has taken a 15 minute break. The 15 minutes need to be continuous rest.

Do you think you might be able to calculate that and show whenever thats not the case?

sam.mckay · June 18, 2018, 8:01pm

Sorry for the delay here, this is a tough one that is taking some thinking power.

sam.mckay · June 20, 2018, 12:48am

I’ve made another start here to a solution that hopefully you can run with.

I have had a good think about this and maybe something as simple as this might actually work within a calculated column

Work Time Start = 
IF( Testing[Work Rest] = "Rest",
    LOOKUPVALUE( Testing[Date],
       Testing[Driver], Testing[Driver],
            Testing[Work Rest], "Work" ) )

See below for the results

Maybe there may have to be an adjustment here if there are multiple values so see if you can work through some logic that might work.

Hopefully this kick start this off again.

Chrs

DavidNealon · June 20, 2018, 9:41am

Hi Sam,

I tried it out but no luck, came back with this error

“A table of multiple values was supplied where a single value was expected.”

I also tried it using the column with the Work Rest State as I thought maybe it was a value type thing but no luck their either.

Regards

Dave

sam.mckay · June 20, 2018, 11:19pm

Are you using different data to what is supplied, because I just took exactly what was placed into the forum. So it’s hard to replicate this at the moment.

What’s different to your data that what I have bee using?

DavidNealon · June 24, 2018, 9:35am

Hi Sam,

I am using different data as I was trying to simplify things.

I’m away for the month without my computer, I’ll load up some actual data when I get back

sam.mckay · June 24, 2018, 9:45pm

Ok sure thing. Yea it doesn’t need to be exact, but just have included some of the nuances of the data as I’m working on the answers and it’s working for me but seems like it doesn’t reflect well in what you’re actually using.

Chrs

DavidNealon · September 18, 2018, 11:53pm

Hi Sam,

I’m having another go at this now.

Trying to bring down the Previous Work or Rest “Date Time” so I can calculate the difference between the two.

I was using this which worked well until I realised it didn’t differentiate between work or rest.

I have a couple of measures, first Duration shown in Hours Minutes Seconds.

Duration = 
FORMAT(
    SUMX('Driver Fatigue Summary Report', 'Driver Fatigue Summary Report'[Duration Decimal]),"hh:mm:ss")

Then I tried this

Previous Date =

CALCULATE( MAX('Driver Fatigue Summary Report'[Date Time]),
FILTER(ALL('Driver Fatigue Summary Report'[Date Time]), 'Driver Fatigue Summary Report'[Date Time] < MAX('Driver Fatigue Summary Report'[Date Time]))

Which worked well but didn’t differentiate between work and Rest. I’ve tried to use the LookupValue with an If Statement but its not playing nice.

Previous Date = 
IF(LOOKUPVALUE('Driver Fatigue Summary Report'[Status],'Driver Fatigue Summary Report'[Status],"Rest"),
    CALCULATE( MAX('Driver Fatigue Summary Report'[Date Time]),
    FILTER(ALL('Driver Fatigue Summary Report'[Date Time]), 'Driver Fatigue Summary Report'[Date Time] < MAX('Driver Fatigue Summary Report'[Date Time]))
         ,0))

Hoping you can help.

Here’s the table I’m working with.Driver Fatigue Summary Report.xlsx (78.9 KB)

I’m also trying to do a lookup for the Lat/Longs as well so I can iterate the distance between them across a row.

Thanks in advance

Dave

sam.mckay · September 19, 2018, 4:50am

What sort of results are you looking for on this.

How are you want to represent the data in your report is what I mean. I’m not 100% sure on the context for example.

I have this mocked up in Power BI but not sure where to next.

Can you show me an example.

This is what I’ve worked up but I’m not really sure if this is what you wanted.

Isn’t it meant to go Work then Rest?

Looking at the data it goes Work Work Work.

If you could explain the logic a little more around this that would be great as well. Chrs