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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tbobolz
Resolver I
Resolver I

Census by Admit Date and Discharge Date

Hi All,

Thanks for any help you cam offer. I've read through a few census examples in the forum; however, I can't seem to find the correct examples.

 

I have data for a year with a admit date format and discharge date. I need to calulate how many patients were in the facility on any given day, and not just the count of when they were admitted. Typically I do this with charge detail and a count of bed charges at midnight; however, I'm trying to minimize the data needed to report out on this. (long story)

 

In the data example below, if I counted how many patients were in house on the 5th of December, it would be 15 or the highlighted patients. Ultimately, I'd like to be able to use this in a matrix and calculate the average patients pay day each month and then total patient "days" in the month. "Days" meaning, if there were 100 patient, each staying for 3 days, then the total days would be 300.

 

Thanks for reviewing and helping if you can. 

 

Terry

 

 

PATIENTENCOUNTER NUMBERADMIT DATE TIMEDISCHARGE DATE TIME
Patient 110012/1/2018 9:1212/2/2018 15:55
Patient 210112/1/2018 6:0712/3/2018 14:37
Patient 310212/2/2018 12:0712/3/2018 16:40
Patient 410312/2/2018 1:1912/4/2018 11:19
Patient 510412/2/2018 4:3512/4/2018 15:10
Patient 610512/4/2018 11:5312/5/2018 12:48
Patient 710612/3/2018 17:2812/5/2018 14:02
Patient 810712/4/2018 9:1612/5/2018 16:57
Patient 910812/3/2018 5:3412/6/2018 12:30
Patient 1010912/4/2018 9:3912/6/2018 12:30
Patient 1111012/2/2018 19:5512/6/2018 12:50
Patient 1211112/3/2018 5:3812/6/2018 14:01
Patient 1311212/4/2018 6:0712/6/2018 14:44
Patient 1411312/5/2018 7:5712/6/2018 14:46
Patient 1511412/5/2018 8:3412/6/2018 14:51
Patient 1611512/4/2018 14:2812/6/2018 18:09
Patient 1711612/5/2018 21:0412/7/2018 10:59
Patient 1811712/4/2018 9:2112/7/2018 11:32
Patient 1911812/6/2018 7:3612/7/2018 12:58
Patient 2011912/6/2018 8:4912/7/2018 13:05
Patient 2112012/4/2018 16:4912/7/2018 15:55
Patient 2212112/1/2018 13:2512/7/2018 16:31

 

 

1 ACCEPTED SOLUTION

After much time and utilizing your examples and other I came up with this solution to add to my current data table shown above.

 

I created a new table under the "Modeling" tab and the first formula was...   Calendar = CALENDARAUTO(12)

This created the first column below titled "Data"

1 - Data

Then I create the following columns from this field

2 - Calendar Year = YEAR([Date])

3 - Calendar Month = MONTH([Date])

4 - MonthStart = DATE([Calendar Year],[Calendar Month],1)

5 - MonthEnd = EOMONTH([Date],12)

 

Next, I created the below formula in my main data table.

Patient Day Count = CALCULATE(COUNTROWS(exec_census), FILTER(exec_census, (([ADMIT DATE TIME] <= LASTDATE('Calendar'[Date])+1) && [Census Discharge Date Time]>= FIRSTDATE('Calendar'[Date]))))
 
Now I can pivot off the Calendar tables [DATA] and [Patient Day Count] fields.
 
This seemd to do the trick, although I'm still fully validating.
 
(NOTE... I had to add the "+1" to my formula above as my dates all have time values all set to 12:00AM. With out the "+1" in the formula, the count ignored the first visit day of each patient.) I'm sure there's a clean way, but I'm still learning.

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

To answer your question, is there any use of the time stamps that appear along with the date or can they be ignored?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The time stamps can be ignored.

 

Thanks.

 

 

Hi,

Since our data formats are different, what is December 5 for you is May 12 for me.  In the image below, you can see the number of patients on May 12 are 15.  You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Thank you for the help. I'm not yet skilled enough to know how you transformed my data format to the distinct count of days you have displayed.

 

Am I creating a separte data table that reads my admit and discharge dates to achieve your fommat? Sorry I am not understanding this.

 

I do greatly apprecated any additional help you can offer.

 

Terry

 

After much time and utilizing your examples and other I came up with this solution to add to my current data table shown above.

 

I created a new table under the "Modeling" tab and the first formula was...   Calendar = CALENDARAUTO(12)

This created the first column below titled "Data"

1 - Data

Then I create the following columns from this field

2 - Calendar Year = YEAR([Date])

3 - Calendar Month = MONTH([Date])

4 - MonthStart = DATE([Calendar Year],[Calendar Month],1)

5 - MonthEnd = EOMONTH([Date],12)

 

Next, I created the below formula in my main data table.

Patient Day Count = CALCULATE(COUNTROWS(exec_census), FILTER(exec_census, (([ADMIT DATE TIME] <= LASTDATE('Calendar'[Date])+1) && [Census Discharge Date Time]>= FIRSTDATE('Calendar'[Date]))))
 
Now I can pivot off the Calendar tables [DATA] and [Patient Day Count] fields.
 
This seemd to do the trick, although I'm still fully validating.
 
(NOTE... I had to add the "+1" to my formula above as my dates all have time values all set to 12:00AM. With out the "+1" in the formula, the count ignored the first visit day of each patient.) I'm sure there's a clean way, but I'm still learning.

Hi,

Go to Home > Edit Queries and study the steps there in the Applied Steps pane.  If you have any doubts, post back.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User


Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

I think the first example is the best use for me. However, I get  little lost in the formula, as it appear to reference multpile tables. Sorry, I am a little green in writting DAX. Below are all the fields I have in one table titled "exec_census". I'm not sure how to set up this formua to relate to only this one table.

 

Thanks for any additiona help you can offer.

 

Terry

 

 

FACILITYUNITENCOUNTER TYPEPATIENTENCOUNTER NUMBERSERVICE TYPEADMIT DATE TIMEDISCHARGE DATE TIMEGENDERRACEPRIMARY_INSURANCE_PLANPATIENT_AT_ADMIT_DTYEARMONTHIsMaxYearIsMaxMonthCasesLOST12Excld CM for Rolling 12 FilterDAYAgeAge CategoryLOS in MintuesDay of WeekHourWeekday
Facility 1Unit 1EmergencyPatient 16000103255Emergency11/17/2018 16:3811/17/2018 18:18MaleCaucasian/WhiteBCBS Bluecard48 Years2018110010.069444444101748Ages 41-64100Sat166
Facility 2Unit 4EmergencyPatient 26000104206Emergency11/24/2018 15:0111/24/2018 16:29MaleCaucasian/WhiteBCBS Bluecard44 Years2018110010.061111111102444Ages 41-6488Sat156
Facility 3Unit8EmergencyPatient 36000105339Emergency12/1/2018 3:2112/1/2018 5:48MaleCaucasian/WhiteBCBS Bluecard25 Years2018120010.10208333310125Ages 19-40147Sat36

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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