- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
08-22-2024 12:58 PM | |||
03-25-2024 06:31 PM | |||
10-14-2023 06:57 AM | |||
09-22-2024 12:51 AM | |||
10-02-2024 07:47 AM |
User | Count |
---|---|
13 | |
13 | |
12 | |
8 | |
7 |
User | Count |
---|---|
18 | |
14 | |
11 | |
11 | |
9 |