topic Re: Hospital Occupancy by Month in DAX Commands and Tips

Hospital Occupancy by Month

Anonymous — Mon, 22 Jan 2024 13:19:05 GMT

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?

Re: Hospital Occupancy by Month

AUaero — Mon, 22 Jan 2024 21:52:36 GMT

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?

Re: Hospital Occupancy by Month

Anonymous — Tue, 23 Jan 2024 13:34:09 GMT

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

Re: Hospital Occupancy by Month

AUaero — Tue, 23 Jan 2024 15:59:22 GMT

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

Re: Hospital Occupancy by Month

Anonymous — Wed, 24 Jan 2024 01:11:39 GMT

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

Re: Hospital Occupancy by Month

Anonymous — Wed, 24 Jan 2024 12:04:17 GMT

Thank you, I'll give this a try!