Calculated column not referencing original entry properly

[jdb0912]

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 ID	Admit Date	Discharge Date	Payer Class	Primary Insurance Carrier
1	1/1/2019	1/2/2019	Federal	Medicaid
2	1/1/2019	1/8/2019	Federal	Dept of VA
3	1/2/2019	1/3/2019	Private	Aetna

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:

Date	Bed Day
1/1/2019	2
1/2/2019	3
1/3/2019	2
1/4/2019	1

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.

[mlsx4 · Accepted solution]

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 · Accepted solution]

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:

[jdb0912]

Hi @mlsx4 ,

Correct me if I'm wrong, but don't I need at least one active relationship?

[mlsx4]

You don't have to. There are some cases where it isn't necessary. Take a look at Microsoft guide: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive

[jdb0912]

Ok, got that part. Now I'm having an issue with what I'm guessing is the time intelligence. My final desired output is a matrix with years as rows, payer class as a subrow, months as columns, and the sum of all bed days for that month and year. At the date level, the output seems correct. Once the data is aggregated at the month/year level though, it displays the value for the last day of the month, which I'm guessing is because MAX(Calendar[Date]) for a month will be the last day of the month. Screenshot below for clarity

[mlsx4]

Hi @jdb0912 

And what do you need to show? A sum, an average?

[jdb0912]

I'd need a sum of the bed days.

[mlsx4 · Accepted solution]

Then, add a new measure:

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

[jdb0912]

Oh I see. I'm supposed to create the measure in the Calendar table and not the Visits table. I think it's working as intended now. Thanks for the help.