Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pabeader
Post Patron
Post Patron

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

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
Tahreem24
Super User
Super User

Hi @pabeader,

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

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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

 

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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.