topic Re: Calculating Occupied beds across multiple months in DAX Commands and Tips

Calculating Occupied beds across multiple months

MIrlam — Tue, 21 Dec 2021 11:02:31 GMT

Hi all

I have a table with date of admission and date of discharge in it. I can calculate the bed occupancy quite easily for people who arrive and leave in the same month. I have custom columns to work out occupancy for people who arrive one month and leave the next which all works. What I can't work out is how to calculate the ones who arrive one month and stay the next (maybe next 2) months. I have tried using if formulas with date diffs and previous and next month expressions but can't work it out!

Ideally and what I've tried to get to work is, I would like one column to work out if the datediff between month of discharge and month of admission is >=2 then add the count of days from the month before discharge to my column

Hopefully I'm on the right lines

Thanks

Re: Calculating Occupied beds across multiple months

amitchandak — Tue, 21 Dec 2021 11:48:29 GMT

MIrlam , A measure like

Countrows(filter(Table, datediff([admission date],[date of discharge],1) > 0 ))

Countrows(filter(Table, Eomonth([admission date],0)<eomonth([date of discharge],0) ))

Re: Calculating Occupied beds across multiple months

MIrlam — Tue, 21 Dec 2021 13:28:21 GMT

Thanks but I don't think I want countrows, I tried it using the first expression and it just returned 2884 in every row in the column.

Re: Calculating Occupied beds across multiple months

Anonymous — Fri, 24 Dec 2021 06:50:00 GMT

Hi MIrlam ,

Here are the steps you can follow：

1. Create calculated column.

Flag = DATEDIFF(EOMONTH('Table'[date_hospitalized],-1)+1,EOMONTH('Table'[date_Discharged],0),MONTH)

day = IF('Table'[Flag] >=2,DATEDIFF('Table'[date_hospitalized],'Table'[date_Discharged],DAY),0)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly