Calculate Duration between two rows based on a group

[bamak35]

Hello! I am really new to Power BI and I think that this problem may be straightforward and it honestly would be much easier for me in my programming background but I am having trouble wrapping my head around it in Power BI. 

What I am trying to accomplish is calculating duration averages based on a job-process-task hierarchy. This data is logging data that contains Start and End times for both tasks and processes, there is no specific table for job logging data. Here is an example of how the data model is laid out:

• JobId
  • ProcessId
    • TaskId
    • TaskId
  • ProcessId
    • TaskId
• JobId
  • ProcessId...

I think you get the picture. I have Task tables that contain the logging information for each task of a specific process. I also have a process table that contains logging information for each process of a job. In a specific task table, it logs each task with its corresponding ProcessId and each task has a start and end time. I have the duration of each task but when I take an average of this column I am getting the average duration of a singular task as opposed to how long the entire process takes. For example, if ProcessId 1 contains 4 tasks that each take 3 minutes to complete - the duration of this instance ProcessId 1 is 12 minutes - but the average will come out as 3 minutes. 

What I need is some way to take the earliest start time and the latest end time and find that difference. In general, the formula would be something like: Average(DateDiff(Min(StartTime),Max(EndTime)) GROUPED BY ProcessId. The logic follows for calculating the average duration of jobs as well where a job may contain various processes so the duration of the job is the difference between the start time of the first process and the end time of the last process. 

I am want to create a measure for this calculation that will allow me to add additional filters to this as well i.e. calculate this same average for the last 7 or 30 days - which is straightforward enough using DATESINPERIOD. But this initial average calculation is throwing me off.

I would greatly appreciate any feedback or advice and also please feel free to ask any clarifying questions if something isn't explained well enough!

Thank you in advance!

[v-shex-msft]

Hi @bamak35,

Can you please share some dummy data with the expected result to help us clarify your requirement and test to coding formula?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Greg_Deckler · Accepted solution]

Perhaps: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

Or maybe 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...