Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
MIrlam
Frequent Visitor

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

vyangliumsft_0-1640328562992.png

 

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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:

vyangliumsft_0-1640328562992.png

 

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
Super User
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)  ))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.