Hospital Occupancy by Month

[joseph_m_liverp]

Hi

I am working with some hospital data and need to calculate the length of stay in hours. The issue I am having is that I need to calculate it for the month so if a stay starts in January and ends in March, I need to be able to calulate the total stay in hours for January only rather than the total stay in hours.

The data looks like this;

Date Table - standard date table from Enterprise DNA.

Hospital Data - arrival date, departure date, total stay in hours, total stay in days.

Can anyone help?

[v-jiewu-msft · Accepted solution]

Hi @joseph_m_liverp ,

If I understand correctly, the issue is that you want to calculate the length of stay in hours for stays within a specific month. Please try the following method and check if they can solve your problem:

1.Create a new measure. Enter the following DAX formula.

LengthOfStayInHoursForMonth = 
VAR CurrentMonthStart = STARTOFMONTH('Date Table'[Date])
VAR CurrentMonthEnd = ENDOFMONTH('Date Table'[Date])
RETURN
SUMX(
    'Hospital Data',
    VAR Arrival = MAX('Hospital Data'[arrival date])
    VAR Departure = MIN('Hospital Data'[departure date], CurrentMonthEnd)
    VAR OverlapStart = MAX(Arrival, CurrentMonthStart)
    VAR OverlapEnd = MIN(Departure, CurrentMonthEnd)
    VAR HoursInMonth = IF(
        OverlapStart <= OverlapEnd, 
        DATEDIFF(OverlapStart, OverlapEnd, HOUR),
        0
    )
    RETURN HoursInMonth
)

2.The measure will calculate the hours for the specific month.

If the above ones can’t help you get it working, could you please provide more raw data(exclude sensitive data) with Text format to make a deep troubleshooting? It would be helpful to find out the solution.

Looking forward to your reply.

Best Regards,

Wisdom Wu

[joseph_m_liverp]

Thank you, I'll give this a try!

[AUaero]

Do you want to attribute the entire stay in hours to the first month of the stay, or are you trying to calculate the hours for each month of a multi-month stay?

[joseph_m_liverp]

Calculate the hours of multi-month stay!

In my head, I am going to connect the date table to the data on both arrival data and departure date, both as inactive relationships but as for the DAX measure, no idea where to go with this

[AUaero]

Can you share a sanitized copy of your dataset or share a sample of the stay data?