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

[tuta23]

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. 

Thanks for your help!

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

[Anonymous]

to just answer you question:

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"

[tuta23]

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

Thanks to all!

[Fowmy · Accepted solution]

@tuta23 

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

You can download the file: HERE

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 🙂

YouTube  LinkedIn

[Greg_Deckler]

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

[tuta23]

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