Hospital Occupancy by Month

Hi Anonymous ,

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