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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Whitewater100 

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.

Whitewater100's avatar
Whitewater100
Solution Sage
4 years ago

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.