Calculating Occupied beds across multiple months

[MIrlam]

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

[v-yangliu-msft · Accepted solution]

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

[amitchandak]

@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)  ))

[MIrlam]

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.