Forum Discussion

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago
Solved

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. Somethin...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Whitewater100 

    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.

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

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!