Forum Discussion
Calculating Occupied beds across multiple months
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
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
3 Replies
- amitchandak
Super User
MIrlam , A measure like
Countrows(filter(Table, datediff([admission date],[date of discharge],1) > 0 ))
or
Countrows(filter(Table, Eomonth([admission date],0)<eomonth([date of discharge],0) ))
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.
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
