cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Time Duration is the End Goal - How Best to Get There

All -- I am trying to set up a PBI report to show the timings of our nightly processes.  There are four processes that we have in our log files with start and end times (assuming it completes, of course).

What I want to get to is to pull how long each process took to complete (if it did), how long did the entire process take to run.  And then show the trends over the last 30 days, etc, etc.

My question is how do I best layout this data to make things easier on myself to calculate durations in PBI?

The TIMEs below are formatted "18:54:56.01", "1:46:19.99 ", etc. --- so we may have times that are technically into the next day that the process actually began.

 Nightly Process TIME Message 8/28/2020 Time 1 Process 1 Start 8/28/2020 Time 2 Process 2 Start 8/28/2020 Time 3 Process 2 Finish 8/28/2020 Time 4 Process 3 Start 8/28/2020 Time 5 Process 3 Finish 8/28/2020 Time 6 Process 4 Start 8/28/2020 Time 7 Process 4 Finish
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion

@tuta23

I prepared some sample data based on your explanation and came up with the following.

1. Each Process Duration by Date

2. Entire Process Duration by Date:

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

5 REPLIES 5
Highlighted
Super User IV

@tuta23 See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Now, since it sounds like you are dealing with duration at some point, see Chelsie Eiden's Duration as you may need it. https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389

@ me in reply if you have any issues.

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Regular Visitor

Thanks Greg.  I think I need a little more help to get to that point.

I think my data needs to get into a better format in order to calculate durations.

Say, something like this:

 Nightly Process Process 1 Start Process 2 Start Process 2 Finish Process 3 Start Process 3 Finish Process 4 Start Process 4 Finish 8/28/2020 DateTime 1 DateTime 2 DateTime 3 DateTime 4 DateTime 5 DateTime 6 DateTime 7 8/29/2020 DateTime 1 DateTime 2 DateTime 3 DateTime 4 DateTime 5 DateTime 6 DateTime 7
Highlighted
Community Champion

@tuta23

I prepared some sample data based on your explanation and came up with the following.

1. Each Process Duration by Date

2. Entire Process Duration by Date:

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

Highlighted
Memorable Member

the best layout is the following:

that you can get by pivoting the table on the  Start-Finish column after splitting your message column :

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3AiIDIwMlHaWQzNxUBUMgI6AoPzm1uFjBUCG4JLGoRClWB4tCIySFRvgUGqModMvMyyzOwK7SBEmlMT4jTVEU4jPSDEmlCT4jzVEUwoyMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Nightly Process" = _t, TIME = _t, Message = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Nightly Process", type text}, {"TIME", type text}, {"Message", type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Message", Splitter.SplitTextByRepeatedLengths(10), {"Message.1", "Message.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Message.1", type text}, {"Message.2", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Message.2]), "Message.2", "TIME")
in
#"Pivoted Column"``````

Highlighted
Regular Visitor

I marked Fowby as solution but there is good stuff to be learned in each reply.

Thanks to all!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors