Forum Discussion
Time Between and Avg Time Question
Based on the average of the whole process, you can refer to the following measure.
Total DateDiff = var _mindate=CALCULATE(MIN('Table'[Time In]),ALLEXCEPT('Table','Table'[Hosp_ID],'Table'[Patient_ID])) var _maxdate=CALCULATE(MAX('Table'[Time Out]),ALLEXCEPT('Table','Table'[Hosp_ID],'Table'[Patient_ID])) return DATEDIFF(_mindate,_maxdate,DAY)Based on the average of each hospital for each step, you can refer to the following measure.
average = var _t= SUMMARIZE('Table',[Step_ID],[Patient_ID], [Hosp_ID],"Diff",[Step DateDiff]) return AVERAGEX(FILTER(_t,[Step_ID]=MAX('Table'[Step_ID]) && [Hosp_ID]=MAX('Table'[Hosp_ID])),[Diff])Finally , you will get a result as shown below (The unit is minutes).
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Based on the average of the whole process, you can refer to the following measure.
Total DateDiff =
var _mindate=CALCULATE(MIN('Table'[Time In]),ALLEXCEPT('Table','Table'[Hosp_ID],'Table'[Patient_ID]))
var _maxdate=CALCULATE(MAX('Table'[Time Out]),ALLEXCEPT('Table','Table'[Hosp_ID],'Table'[Patient_ID]))
return DATEDIFF(_mindate,_maxdate,DAY)Based on the average of each hospital for each step, you can refer to the following measure.
average =
var _t= SUMMARIZE('Table',[Step_ID],[Patient_ID], [Hosp_ID],"Diff",[Step DateDiff])
return AVERAGEX(FILTER(_t,[Step_ID]=MAX('Table'[Step_ID]) && [Hosp_ID]=MAX('Table'[Hosp_ID])),[Diff])Finally , you will get a result as shown below (The unit is minutes).
I have attached my pbix file , you can refer to it .
Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Ailsa:
Thank so much at looking at my question. I have one very much related but not explained well enough the first time.
I am also 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/1HxCTXtp0CVGi9k4lVFNJdu8eCnsaH4iU/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
