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 form...
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.
tbobolz's avatar
tbobolz
Icon for Resolver I rankResolver I
7 years ago

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

 

tbobolz's avatar
tbobolz
Icon for Resolver I rankResolver I
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.