Days to Complete a process

[Whitewater100]

Hello Community:

I am trying to compare the hospitals on how many days does it take on average(Hospital Average) to get thru all four treatments. 

I will insert link for file with explanation on page 3. The data is  not exact - just showing one possible result.

I will also paste below an an example on how I got the averages for the NC Hospital for each step.

Thanks for any input on getting the Hospital Average to Cover(Days) the range of Steps 1 thru 4, able to be totalled and also filterable by hosptial or patient.

https://drive.google.com/file/d/1snUbqhFrNDCKNyFBhJAa4Nnyg_dxabbB/view?usp=sharing

Hospt	Patient	Type	Step	Time In	Time Out	Days	Forumla
NC	A	Radiology	Step 1	5/2/2022 12:05 p.m.	5/2/2022 1:05 p.m.	1
NC	B	Radiology	Step1	5/2/2022 1:05 p.m.	5/2/2022 3:05 p.m.	1
NC	A	Radiology	Step 2	5/4/2022 12:05 p.m.	5/4/2022 1:05 p.m.	1.95	Time out 5-04 -Time Out 5-02
NC	B	Radiology	Step 2	5/4/2022 1:05 p.m.	5/4/2022 2:05 p.m.	1.95	Time out 5-04 -Time Out 5-02
NC	A	Radiology	Step 3	5/9/2022 12:05 p.m.	5/9/2022 1:05 p.m.	5	Time out 5-09 -Time Out 5-04
NC	B	Radiology	Step 3	5/9/2022 1:05 p.m.	5/9/2022 2:05 p.m.	5	Time out 5-09 -Time Out 5-04
NC	A	Radiology	Step 4	5/16/2022 12:05 p.m.	5/16/2022 2:05 p.m.	7.04	Time out 5-16 -Time Out 5-09
NC	B	Radiology	Step 4	5/16/2022 1:05 p.m.	5/16/2022 2:05 p.m.	7	Time out 5-16 -Time Out 5-09
				AVG DAYS (all patients)
		NC	Step 1	1
		NC	Step 2	1.95
		NC	Step 3	5
		NC	Step 4	7.02
			Tot Avg	14.97

The finaloutput would directionally look like this:

Thanks!

[vapid128 · Accepted solution]

In Table add colnum

days = 
if([Step_ID]=1,
    1,
    [Time Out]-LOOKUPVALUE('Table'[Time Out],'Table'[Step_ID],'Table'[Step_ID]-1,'Table'[Patient_ID],'Table'[Patient_ID])
)

add measure

aveDays = AVERAGEX(VALUES('Table'[Patient_ID]),CALCULATE(SUM('Table'[days])))

[Whitewater100]

Thanks very much. Nice job!