Forum Discussion
Calculated column not referencing original entry properly
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:
Then, add a new measure:
Sum of bed days= SUMX(CalendarTable,[Active beds])
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:
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
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