Forum Discussion

tbobolz's avatar
tbobolz
Icon for Resolver I rankResolver I
7 years ago
Solved

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

 

 

Need Help
  • tbobolz's avatar
    tbobolz
    7 years ago

    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.

8 Replies

    • tbobolz's avatar
      tbobolz
      Icon for Resolver I rankResolver I
      7 years ago

      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
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

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

    • tbobolz's avatar
      tbobolz
      Icon for Resolver I rankResolver I
      7 years ago

      The time stamps can be ignored.

       

      Thanks.

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        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.