Alright,
So the hard part is to find the number of patients pr night and sum it over a month. The number of beds should be simpler, at least if the number of beds are fairly static.
First I added an index to the Internacao-table in power query, in order to make the measure a bit slimmer.
Next I could not identify a date/calendar table, so I created one. It only contains date and months.
The measure then looks like this:
Number of patients pr day =
VAR m =
SELECTEDVALUE ( Dates[month] )
RETURN
COUNTROWS (
CALCULATETABLE (
GENERATE (
SUMMARIZE (
INTERNACAO;
INTERNACAO[Index];
INTERNACAO[data_internacao];
INTERNACAO[alta_medica]
);
FILTER (
Dates;
dates[Date] >= CALCULATE ( VALUES ( INTERNACAO[data_internacao] ) )
&& dates[Date] <= CALCULATE ( VALUES ( INTERNACAO[alta_medica] ) )
)
);
FILTER ( Dates; dates[month] = m )
)
)In order for this code to work, Dates[Month] needs to be on the axis.
How this code works, it starts with the Generate-function. The first argument of this function is the Summarize of index, hospitalization date and discharge date. It would have been possible to skip the summarize and just used the full Iternacao-table, but then much more data would need to be stored in memory. The second argument of Generate is evaluated for each row of the first argument, and the dates are filtered by hospitalization date and discharge date for each customer. The index works as an unique identifier for each hospitalization. The resulting table is a table where each index has rows for all dates between hospitalization and discharge. To get it pr month, there is a calculatetable around the generate-statement, which only works if there is only one distinct month value in the current context. After that it is just a matter of counting the rows. Check out index=574 to see that it works(hospitalizes in may, discharged in july). There are some gritty details which are not handled, for instance index=1 where hospitalization is at 01.05.2019 00:31 and discharged at 01.05.2019 09:05.
https://www.dropbox.com/s/kbsw5080bsppfz9/hospital%20occupancy%20rate%20Original.pbix?dl=0
cheers,
S
