Length of Patient Admissions

[Clinical_Epi]

Hi PBi folks,

We are using Power BI to show the length of Patient stay within the Hospital, what we are trying to do at the moment is work out the Length of stay regardless of an espisode of care change in the Patient record. Our programmer can possibly work this out in SQL - but I'm hoping to be able to do this in Power Bi also.

Below is a sample table to show the issue:

Where Patient 00001234 is admitted on 01/04/2019 and has an Episode of Care change on 02/04/2019 the Length of Stay (los_mins) is "1041"..... we really need a new column that shows the los_mins from 01/04/2019 to 08/04/2019 which would be "9960" - ignoring the Episode of Care change. 

We can exclude the "admin_disch" EOC change to get a real count, but its the actual length of stay we need to show.

customer_id	start_date_time	end_date_time	los_mins	EOC_change
00001234	28/03/2019 22:19	29/03/2019 11:42	803	Home
00001234	01/04/2019 17:19	02/04/2019 10:40	1041	admin_disch
00001234	02/04/2019 10:41	08/04/2019 10:20	8619	Home

Many thanks in advance if anyone is able to solve this.

Kind Regards,

ClinEPi

[DataInsights · Accepted solution]

@Clinical_Epi,

Try this calculated column:

actual los = 
VAR vCust = Patients[customer_id]
VAR vStartAdmin = Patients[start_date_time]
VAR vLOSAdmin = Patients[los_mins]
VAR vStartHome =
    CALCULATE (
        MIN ( Patients[start_date_time] ),
        FILTER (
            Patients,
            Patients[customer_id] = vCust
                && Patients[start_date_time] > vStartAdmin
                && Patients[EOC_change] = "Home"
        )
    )
VAR vLOSHome =
    CALCULATE (
        MIN ( Patients[los_mins] ),
        FILTER (
            Patients,
            Patients[customer_id] = vCust
                && Patients[start_date_time] = vStartHome
        )
    )
VAR vLOSTotal = vLOSAdmin + vLOSHome
VAR vResult =
    IF ( Patients[EOC_change] = "admin_disch", vLOSTotal )
RETURN
    vResult

[Clinical_Epi]

Hello, 

Thank you so much for the code - i've loaded it in as a new column, but unfortunately its not working... I get 1041... Not sure where its gone wrong but I will see if I can get it to play ball.... ps: I just loaded it into a blank app as a new table - renamed the table as "Patients" and pasted the code you sent as a new column. 

Regards

Clin Epi

[Clinical_Epi]

****I found a space preceeding one of the "Home" cells - that being resolved it is adding the 2 cells, however this problem revolves around the "Home" nature of seperation - which not being accounted for in your code example unfortunately. What we would want is a result where there is a count for those who just go home as well. As they are on seperate rows its near impossible to handle this by a work around on the code sample above 😞  - keeping in mind we're running this through thousands of lines of code and are looking fo rthe actual Length of Stay everytime a patient comes into the hospital. So to explain a bit more - if they had an administrative discharge, they done go home until later. Also they might come in a week later and not have an adminsitrative discharge and go home. We'd want to include those too. 🙂 

[DataInsights]

@Clinical_Epi,

Would you be able to provide an example of each scenario (sample data that I can paste into Power BI, and the expected result)?

[Clinical_Epi]

Thnks for getting back to me and appologies for the late response, for anyone reading this = we ended up resolving it in SQL, as was easier in the end.