Creating a hospital census dashboard

Hi ErDrRon ,

1.When you import the data into Power BI, it looks like this in the Power Query Editor.

2.Right-click the field name and select Split by positions.

3.It will intelligently figure out where you want to split, we remove the 8 and click ok.

3.Remove unnecessary columns and rename other columns. Click Close&Apply.

4.Create a calendar table. No relationships between tables.

Table 2 = CALENDAR(DATE(2021,1,1),DATE(2021,1,10))

5.Create the measure and put into the matrix.

Measure = 
IF(
        MAX('Table'[Admitted Date]) <= MAX ( 'Table 2'[Date] )
            && MAX('Table'[Discharged Date]) >= MAX ( 'Table 2'[Date] )
,"In Hospital"
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.