Forum Discussion
Time Between and Avg Time Question
Hi:
I need to work on the time measures in PBI. They are both concerned with STeps 1 -4.
I have a amde up scenario where a patient goes thru a four - step process to complete a therapy. Something like 4 treatments and they are finished.
The user could select one step from slicer or two steps from same slicer to compare the Hospitals service time.
I'm looking for two meaures:
#1. How much time it takes to complete Step 1 to Step 2, Step3, & Step 4( total time). Days and Minutes or hours is fine. Basically compare any two steps.
#2. Same question as above but based on average time.
In the example below it takes 2 days to complete Step1 thru Step 2 and the 2 appointment's time average is 2 hours 10 minutes(130 minutes).
I will attach the file to make it a bit easier to check. In the end I want to compare the hospitals against eachother to see the comparable metrics..
https://drive.google.com/file/d/1snUbqhFrNDCKNyFBhJAa4Nnyg_dxabbB/view?usp=sharing
Thanks very much!
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.
6 Replies
Hi Again:
To explain expected results a bit more clearly. For Patient A , the time between In time on 5-02 and out time on 5-04 is almost two days.
For Step 2 to 3. 5-04 1:05 to 5-09 1:05 pm = 5 Days
For Step 3 to Step 4 = 5-09 1:05 pm to 5-16 2:05 pm is a little more than 7 Days.
Total Start to End is 5-02 1:05 pm to 5-16 2:05 pm which is a little more that 14 Days. 14.041 days.
The idea is to be able to do this same type of calculation to obtain the four hospitals average for each step and in total.
I hope this helps explain the scenario more clearly.
Thanks for any input on this one!
I have a doubt, if I choose GA in hospital slicer , it returns two patients E and F . For E, his time is from 5-02 2:05 pm to 5-17 3:05 pm,the duration is 15 days plus one hour . For F, his time is from 5-04 2:05 pm to 5-17 5:05 pm,the duration is 13 days plus three hours . So for GA, the average is 15 days 1 hour plus 13 days 3 hours, divided by 2 ? The end result is 14 days plus 2 hours?
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 you for replying. Yes, you are understanding correctly for the scenario of the total time for the 4 steps for GA. Same answer basically for NC.
The other part is figuring time for the steps to complete in the same way. So for GA between Step 1 & Step 2: Patient 5 almost 4 days and patient 6 a bit over 3 days. So average is around 3.6 days. See data below
Besides the total question ,there are Step 1 -2. Step 2 -3. Step 3-4. Thanks very much for your help.
Hello Again:
One potential comparion whould look like the chart below(with a total time elapsed between the start and end, it's also great if can filter by patient too.
I hope this helps explain:
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
