working day, total working day, running total

[kattlees]

I am super close to getting what I need.  I need to get total time a surgeon was in the OR. This Matrix is right EXCEPT the row and column totals.

Each day it is calculating HOURS from First Patient In time to Last Patient Out.  But I see in the totals it is doing the time of the first patient in to last patient out no matter what day it is. The totals I am looking for are in red along with a list of BALLARD on his cases.

I do have a rank system set up so it ranks the cases by start time 1 to ? for the day. I'm figuring I am missing a filter or something to limit by the day.

My first case of day measure is First Case of Day = MIN(V_EWS_TASK_RECORD[OR Times Final.Patient In])

My Last case of day measure is Last Case of Day = MAX(V_EWS_TASK_RECORD[OR Times Final.Patient Out])

Total OR Time is Total OR Time Surgeon = DATEDIFF([First Case of Day],[Last Case of Day],MINUTE)/60

[v-xjiin-msft]

Hi @kattlees,

=>But I see in the totals it is doing the time of the first patient in to last patient out no matter what day it is.

Based on your shared measure formula First Case of Day or Last Case of Day. It is calculating the MIN First Patient In time or MAX Last Patient Out for entire source table. Not for single Day group or Provider group. As in your shared sample, it is calculating First patient in time on 2-9-18 (10:27) to last patient out time on 2-2-18(15:46). Beacause for this entire shared sample, the MIN in time is 10:27 and the MAX out time is 15:46. It is not based on Day group.

To calculate the MIN or MAX time for each day, you can add ALLEXCEPT() function in your formula. Please refer to following expression:

First Case of Day =
CALCULATE (
    MIN ( V_EWS_TASK_RECORD[OR Times Final.Patient In] ),
    ALLEXCEPT ( V_EWS_TASK_RECORD, V_EWS_TASK_RECORD[Day] )
)

If it doesn't resolve your issue. Please share us more information like some sample data and its desired result which can help us repro your issue and make some test.

Thanks,
Xi Jin.

[kattlees]

I tried this and it gets me the same total time for every provider.

Here is a screen shot and the red is what it should be. The line items are the data it's pulling from

The first case of the day and last case of the day aren't working.  The formulas you gave me didn't specify by Provider.

[v-xjiin-msft · Accepted solution]

Hi @kattlees,

In your scenario, you want to sum of total time and display the value in Sub Total of matrix. Right?

To achieve that, we should do one more step for total time. Please refer:

Also, I'm using my above solution to get the First case and Last case.

First Case =
CALCULATE (
    MIN ( V_EWS_TASK_RECORD[First Case of Day] ),
    ALLEXCEPT (
        V_EWS_TASK_RECORD,
        V_EWS_TASK_RECORD[Provider Short],
        V_EWS_TASK_RECORD[Date]
    )
)

Then we need to a sum for the total time before we put it into the Values of matrix. So that the Sub Total in Matrix will be sumed.

Total Hours =
SUMX (
    SUMMARIZE (
        V_EWS_TASK_RECORD,
        V_EWS_TASK_RECORD[Provider Short],
        V_EWS_TASK_RECORD[Date]
    ),
    [Last Case] - [First Case]
)

If it still doesn't satisfy your requirement, please kindly share us some sample data which we can copy and paste directly. Otherwise I have to type all of them one by one.

Thanks,
Xi Jin.