Forum Discussion

jdb0912's avatar
jdb0912
New Member
3 years ago
Solved

Calculated column not referencing original entry properly

Hi everyone,

 

I wasn't sure how to really title the exact issue I was facing, so I explained it as best as I could below.

 

I'm trying to calculate bed days, then associate the insurance information to each bed day. I'm working with a dataset that looks something like:

Visit IDAdmit DateDischarge DatePayer ClassPrimary Insurance Carrier
11/1/20191/2/2019FederalMedicaid
21/1/20191/8/2019FederalDept of VA
31/2/20191/3/2019PrivateAetna

 

In order to calculate bed days, I made a date table with a dynamic range starting with the start of the 4 years ago, defined with DAX:

Calendar = CALENDAR(DATE(YEAR(TODAY()) - 4, 1, 1), TODAY())

 

Then I make a calculated column within the calendar table for the bed days using DAX:

BedDays = CALCULATE(DISTINCTCOUNT(Visits[VisitID]), FILTER(ALL(Visits), (Visits[AdmitDate] <= Calendar[Date] && InpatientVisits[AdmitDate] >= Calendar[Date])  || (Visits[AdmitDate] <= Calendar[Date] && ISBLANK(Visits[DischargeDate])))

 

The calendar table will look like this as a result:

DateBed Day
1/1/20192
1/2/20193
1/3/20192
1/4/20191

 

I established a relationship between the visits table and the calendar table on the discharge date and calendar date.

 

I was hoping to achieve something where I can break down the bed days per day/month/year based on their insurance information. However, I can't seem to figure it out. If for example I'm using a matrix with the following configuration:

and there are 97 bed days for 1/1/2019, 50 federal and 47 private, I will get the below picture instead.

 

 

Thanks for any guidance.

Need Help
  • mlsx4's avatar
    mlsx4
    3 years ago

    Hi jdb0912 

     

    First of all you should establish two inactive relationship: between Admit Date and Calendar and Discharge Date and Calendar.

     

    Then, you can calculate active beds (with this formula you also check if discharge date is empty in case the patient is still on it:

     

     

    Active beds= 
    CALCULATE(DISTINCTCOUNT('Table'[Visit ID]),
    FILTER('Table','Table'[Admit Date]<=MAX('Calendar'[Fechas]) && OR('Table'[Discharge Date] >=MAX('Calendar'[Fechas]),ISBLANK('Table'[Discharge Date]))))

     

     

     

    And now, you can display it in a matrix:

     

     

  • mlsx4's avatar
    mlsx4
    3 years ago

    Then, add a new measure:

     

    Sum of bed days= SUMX(CalendarTable,[Active beds])

8 Replies

  • mlsx4's avatar
    mlsx4
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi jdb0912 

     

    First of all you should establish two inactive relationship: between Admit Date and Calendar and Discharge Date and Calendar.

     

    Then, you can calculate active beds (with this formula you also check if discharge date is empty in case the patient is still on it:

     

     

    Active beds= 
    CALCULATE(DISTINCTCOUNT('Table'[Visit ID]),
    FILTER('Table','Table'[Admit Date]<=MAX('Calendar'[Fechas]) && OR('Table'[Discharge Date] >=MAX('Calendar'[Fechas]),ISBLANK('Table'[Discharge Date]))))

     

     

     

    And now, you can display it in a matrix: