I have a timeseries data of list of processes uploaded to PowerBI desktop.
The data is composed of multiple processes and sub process and their start and finish time. Each process has multiple sub processes. Each sub process (of a parent process) is scheduled to run everyday. Sub processes run concurrently. For a process to be completed, all sub processes (which belong to that particular process) should be finished.
Start time and finish time for each sub process is recorded.
Below is a sample data for illustration purposes. I need to calculate the elapsed time for each distinct process for each day. To calculate the elapsed time for a process, we need to find sub process started earliest before all other sub processes and we also need to find the sub process finished the latest and then we need to take the time difference between the finish time and start time (in minutes) to calculate the elapsed time for that process.
For example, in Table1, process P1 has 3 sub processes and among all 3 sub processes, the one that starts the earliest is P1_Sub (which has a start time of "1/01/2021 9:00") and the sub process that finishes the latest is P1_Sub3 (which has a finish time of "1/01/2021 9:30"). So the elapsed time for the process P1 on the day 1/01/2021 was 30 minutes (which is the time difference between the earliest start time and the latest finish time for its sub processes)
I need to calculate the elapsed time for each process for each day similar to Table2 below.
Can you please help me with the DAX formula?
Thanks
Andy
