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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Allisond
Advocate II
Advocate II

Calculating Total Census for Date Range

Hello everyone,
I have a table with              PtID           Start Date                  Billing End Date 
I need to calculate occupancy based on date range and include the start date and end date in the days.   I am using this measure, but it is not capturing all the patients.   Can someone help me with what I am missing?
 
Occupancy =
VAR StartDate = VALUE(SELECTEDVALUE( 'Census Detail'[StartDate] ) )
VAR BillingEndDate = VALUE(SELECTEDVALUE( 'Census Detail'[Billing End Date] ) )
VAR MinDate = VALUE( MIN( Dates[DayDate] ) )
VAR MaxDate = VALUE( MAX( Dates[DayDate] ) )

 

RETURN
IF( AND( StartDate <= MinDate, BillingEndDate >= MinDate ) ,
MIN(BillingEndDate, MaxDate ) - MinDate,
IF( AND( AND( StartDate >= MinDate, StartDate <= MaxDate), BillingEndDate >= MinDate),
MIN( BillingEndDate, MaxDate) - AdmitDate,
"0" ) )
 
Then I create Census = SUMX( VALUES('Billing Census Detail'), [Occupancy])
 
Any help would be greatly appreciated.
1 REPLY 1
v-diye-msft
Community Support
Community Support

Hi @Allisond 

 

You might consider providing your dummy pbix that would be folpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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