Forum Discussion
Resolver ICensus by Admit Date and Discharge Date
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.
Community Champion
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
Resolver IHi 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 |