cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pabeader Frequent Visitor
Frequent Visitor

How to calculate duration in job with timestamp in one column?

I'm trying to create a measure or calculated column that would give the amount of time, in minutes, that a person spent doing a task.

 

Here is a sample of the raw data:

TransIDPersonIDJobTimeStamp
255Sue1007/20/19 10:00 PM
257Ken1017/20/19 10:01 PM
258Kay1127/20/19 10:01 PM
259Bill1017/20/19 10:02 PM
260Ken1007/20/19 10:03 PM
261Joe1017/20/19 10:03 PM
262Bill1207/20/19 10:03 PM
263Matt1017/20/19 10:04 PM
264Bill1007/20/19 10:04 PM
265Don1017/20/19 10:05 PM
266Lee1017/20/19 10:05 PM
297Steve1207/20/19 10:28 PM
298Bill1017/20/19 10:29 PM
299Steve1007/20/19 10:29 PM

Here is an example of the output I would like:

FilterJobTimeStampDuration
Bill1017/20/19 10:02 PM00:01
 1207/20/19 10:03 PM00:01
 1007/20/19 10:04 PM00:25

 

Other filters would give a similar list for that person.

 

 

I appriciate any pointers or assistance I can get.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: How to calculate duration in job with timestamp in one column?

Hi @pabeader ,

 

Based on your data posted at the beginning of this message, I managed to get the output as

 

WorkingTime.JPG

Was this the output you were expecting?

Note that Bill Last TimeStamp was 20/7/2019 10:29:00 PM on the Job 101. As this is the last record and no more records for Bill, the duration is shown as blank > Meaning the job under WIP.

 

To do this I loaded the table and using Query Editor did the following steps.

let
    Source = Csv.Document(File.Contents("C:\PowerBICommunity\sample.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TransID", Int64.Type}, {"PersonID", type text}, {"Job", Int64.Type}, {"TimeStamp", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}}),
    #"Partition" = Table.Group(#"Sorted Rows", {"PersonID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"TransID", "Job", "TimeStamp", "Index"}, {"TransID", "Job", "TimeStamp", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition1",{{"TimeStamp", type datetime}, {"Index", Int64.Type}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}})
in
    #"Sorted Rows1"

 

This basically

a) sorts the raw data by PersonId, TImeStamp

b) Creates Index by PersonId

 

Then Created a calculated column  TimeTaken

 


TimeTaken =

VAR CurrentTime = WorkingTime[TimeStamp]

VAR CUrrentID = WorkingTime[PersonID]

VAR CurrentIndex = WorkingTime[Index]

VAR NextTime =

    CALCULATE(VALUES(WorkingTime[TimeStamp]),

         FILTER(ALL(WorkingTime),

         WorkingTime[PersonID] = CUrrentID &&

         WorkingTime[Index] = CurrentIndex +1

         ))

    

RETURN

       DATEDIFF(CurrentTime,NextTime,MINUTE)

 

Check it out

 

Cheers

CheenuSing

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Super User II
Super User II

Re: How to calculate duration in job with timestamp in one column?

Hi @pabeader,

COuld you please share breif on how to Calculate "Duration" column in expected output table?

pabeader Frequent Visitor
Frequent Visitor

Re: How to calculate duration in job with timestamp in one column?

An example of the calculation would be:

Filter by Bill so that you have a list of his entries.

When he goes from job 101 to job 120.  Use the Timestamp at 120 as the 'end' time for the 101 entry.  The duration is the 'end' timestamp minus the timestamp at 101.  Then when he goes from 120 to 100, use the Timestamp at 100 as the 'end' time for the 120 entry.  Continue this until you run out of entries. 

Note: There are no overlaps, there should be no repeats and there is no 'missing' time.  The start of a job is the end of the previous one.

 

So filtered Bill might look like this:

TransIDPersonIDJobTimeStamp
259Bill1017/20/19 10:02 PM
262Bill1207/20/19 10:03 PM
264Bill1007/20/19 10:04 PM
298Bill1017/20/19 10:29 PM
300Bill1007/20/19 10:31 PM
323Bill1017/20/19 10:56 PM
326Bill1207/20/19 10:58 PM
331Bill1007/20/19 11:01 PM
362Bill1017/20/19 11:27 PM
365Bill1207/20/19 11:28 PM
369Bill1117/20/19 11:30 PM

 

And Final table would look lke this:

FilterJobTimeStampDuration
Bill1017/20/19 10:02 PM00:01
 1207/20/19 10:03 PM00:01
 1007/20/19 10:04 PM00:25
 1017/20/19 10:29 PM00:02
 1007/20/19 10:31 PM00:25
 1017/20/19 10:56 PM00:02
 1207/20/19 10:58 PM00:03
 1007/20/19 11:01 PM00:26
 1017/20/19 11:27 PM00:01
 1207/20/19 11:28 PM00:02
 1117/20/19 11:30 PM00:06

 

Super User I
Super User I

Re: How to calculate duration in job with timestamp in one column?

Hi @pabeader ,

 

Based on your data posted at the beginning of this message, I managed to get the output as

 

WorkingTime.JPG

Was this the output you were expecting?

Note that Bill Last TimeStamp was 20/7/2019 10:29:00 PM on the Job 101. As this is the last record and no more records for Bill, the duration is shown as blank > Meaning the job under WIP.

 

To do this I loaded the table and using Query Editor did the following steps.

let
    Source = Csv.Document(File.Contents("C:\PowerBICommunity\sample.csv"),[Delimiter=",", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TransID", Int64.Type}, {"PersonID", type text}, {"Job", Int64.Type}, {"TimeStamp", type datetime}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}}),
    #"Partition" = Table.Group(#"Sorted Rows", {"PersonID"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"TransID", "Job", "TimeStamp", "Index"}, {"TransID", "Job", "TimeStamp", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Partition1",{{"TimeStamp", type datetime}, {"Index", Int64.Type}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"PersonID", Order.Ascending}, {"TimeStamp", Order.Ascending}})
in
    #"Sorted Rows1"

 

This basically

a) sorts the raw data by PersonId, TImeStamp

b) Creates Index by PersonId

 

Then Created a calculated column  TimeTaken

 


TimeTaken =

VAR CurrentTime = WorkingTime[TimeStamp]

VAR CUrrentID = WorkingTime[PersonID]

VAR CurrentIndex = WorkingTime[Index]

VAR NextTime =

    CALCULATE(VALUES(WorkingTime[TimeStamp]),

         FILTER(ALL(WorkingTime),

         WorkingTime[PersonID] = CUrrentID &&

         WorkingTime[Index] = CurrentIndex +1

         ))

    

RETURN

       DATEDIFF(CurrentTime,NextTime,MINUTE)

 

Check it out

 

Cheers

CheenuSing

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

pabeader Frequent Visitor
Frequent Visitor

Re: How to calculate duration in job with timestamp in one column?

This solution works with just a tiny bit of tweaking.

When you created the table query you named it WorkingTime but I can't see where that was noted.  Only matters when we get to the calculated column part of the solution.  Once I caught that, it worked great!!

 

As far as the actual solution goes.  Good gravy!  It's going to take me a few days to figure out how it actually works.  But it sure works.

 

Thanks for the good work!

 

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors