cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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. 

 

Thanks for your help!

 

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

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

@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

Fowmy_0-1599892919407.png


2. Entire Process Duration by Date:

Fowmy_1-1599892956245.png

________________________

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

 



 

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

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

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


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

@ me in replies or I'll lose your thread!!!

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

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

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 ProcessProcess 1 StartProcess 2 StartProcess 2 FinishProcess 3 StartProcess 3 FinishProcess 4 StartProcess 4 Finish
8/28/2020DateTime 1DateTime 2DateTime 3DateTime 4DateTime 5DateTime 6DateTime 7
8/29/2020DateTime 1DateTime 2DateTime 3DateTime 4DateTime 5DateTime 6DateTime 7
Highlighted
Community Champion
Community Champion

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

@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

Fowmy_0-1599892919407.png


2. Entire Process Duration by Date:

Fowmy_1-1599892956245.png

________________________

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

 



 

View solution in original post

Highlighted
Memorable Member
Memorable Member

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

to just answer you question:

the best layout is the following:

image.png

 

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

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

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

 

Thanks to all!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors