Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
PATIENT | ENCOUNTER NUMBER | ADMIT DATE TIME | DISCHARGE DATE TIME |
Patient 1 | 100 | 12/1/2018 9:12 | 12/2/2018 15:55 |
Patient 2 | 101 | 12/1/2018 6:07 | 12/3/2018 14:37 |
Patient 3 | 102 | 12/2/2018 12:07 | 12/3/2018 16:40 |
Patient 4 | 103 | 12/2/2018 1:19 | 12/4/2018 11:19 |
Patient 5 | 104 | 12/2/2018 4:35 | 12/4/2018 15:10 |
Patient 6 | 105 | 12/4/2018 11:53 | 12/5/2018 12:48 |
Patient 7 | 106 | 12/3/2018 17:28 | 12/5/2018 14:02 |
Patient 8 | 107 | 12/4/2018 9:16 | 12/5/2018 16:57 |
Patient 9 | 108 | 12/3/2018 5:34 | 12/6/2018 12:30 |
Patient 10 | 109 | 12/4/2018 9:39 | 12/6/2018 12:30 |
Patient 11 | 110 | 12/2/2018 19:55 | 12/6/2018 12:50 |
Patient 12 | 111 | 12/3/2018 5:38 | 12/6/2018 14:01 |
Patient 13 | 112 | 12/4/2018 6:07 | 12/6/2018 14:44 |
Patient 14 | 113 | 12/5/2018 7:57 | 12/6/2018 14:46 |
Patient 15 | 114 | 12/5/2018 8:34 | 12/6/2018 14:51 |
Patient 16 | 115 | 12/4/2018 14:28 | 12/6/2018 18:09 |
Patient 17 | 116 | 12/5/2018 21:04 | 12/7/2018 10:59 |
Patient 18 | 117 | 12/4/2018 9:21 | 12/7/2018 11:32 |
Patient 19 | 118 | 12/6/2018 7:36 | 12/7/2018 12:58 |
Patient 20 | 119 | 12/6/2018 8:49 | 12/7/2018 13:05 |
Patient 21 | 120 | 12/4/2018 16:49 | 12/7/2018 15:55 |
Patient 22 | 121 | 12/1/2018 13:25 | 12/7/2018 16:31 |
Solved! Go to 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.
Hi,
To answer your question, is there any use of the time stamps that appear along with the date or can they be ignored?
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.
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.
Hi,
Go to Home > Edit Queries and study the steps there in the Applied Steps pane. If you have any doubts, post back.
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
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
FACILITY | UNIT | ENCOUNTER TYPE | PATIENT | ENCOUNTER NUMBER | SERVICE TYPE | ADMIT DATE TIME | DISCHARGE DATE TIME | GENDER | RACE | PRIMARY_INSURANCE_PLAN | PATIENT_AT_ADMIT_DT | YEAR | MONTH | IsMaxYear | IsMaxMonth | Cases | LOS | T12 | Excld CM for Rolling 12 Filter | DAY | Age | Age Category | LOS in Mintues | Day of Week | Hour | Weekday |
Facility 1 | Unit 1 | Emergency | Patient 1 | 6000103255 | Emergency | 11/17/2018 16:38 | 11/17/2018 18:18 | Male | Caucasian/White | BCBS Bluecard | 48 Years | 2018 | 11 | 0 | 0 | 1 | 0.069444444 | 1 | 0 | 17 | 48 | Ages 41-64 | 100 | Sat | 16 | 6 |
Facility 2 | Unit 4 | Emergency | Patient 2 | 6000104206 | Emergency | 11/24/2018 15:01 | 11/24/2018 16:29 | Male | Caucasian/White | BCBS Bluecard | 44 Years | 2018 | 11 | 0 | 0 | 1 | 0.061111111 | 1 | 0 | 24 | 44 | Ages 41-64 | 88 | Sat | 15 | 6 |
Facility 3 | Unit8 | Emergency | Patient 3 | 6000105339 | Emergency | 12/1/2018 3:21 | 12/1/2018 5:48 | Male | Caucasian/White | BCBS Bluecard | 25 Years | 2018 | 12 | 0 | 0 | 1 | 0.102083333 | 1 | 0 | 1 | 25 | Ages 19-40 | 147 | Sat | 3 | 6 |
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |