Calculating the average number of customers/patients in by hour in DAX

[tuomo_kareoja]

Im working with hospital data and I would want to calculate how many new patients come into the hospital and how many patients are in the hospital on average by hour. In short the goal is to draw this picture:

Y = number of patients, X = hour, blue = how many patients currently in, green = how many new patients arrive

In the data there is a row for each stay with two timestamps: one for coming into the hospital and one for leaving the hospital. Its is possible to stay overnight and this has to be taken into account in the calculation.

There are easy ways to do this by aggregating the data into a suitable format in the database or in power query editor, but is there a way to do this dynamically in DAX without modifying the data beforehand? 

PS: There reason I am asking this is that I would like to give the user a possibility to change the length of stay of certain group dynamically to explore different scenarios (using the new parameter option). I haven't found a way to do this with static aggregation.

[tuomo_kareoja · Accepted solution]

Thanks! This is great, but only solves half of the problem. I would still need to calculate the numbers of patients in the hospital at each hour.

[v-qiuyu-msft]

Hi @tuomo_kareoja,

In your scenario, you can create a calendar date time table: 

DateTime = SELECTCOLUMNS (
ADDCOLUMNS (
CROSSJOIN (
CALENDAR ( "2017-09-01", TODAY() ),
SELECTCOLUMNS (
ADDCOLUMNS ( CALENDAR ( "2016-01-01", "2016-01-24" ), "DAY", DAY ( [Date] ) ),
"DAY", [DAY]
)
),
"dateTime", [Date]
+ IF ( [DAY] = 24, 0, [DAY] )
/ 24
),
"Date", [Date],
"DateTime", [dateTime]
)

Then create measures: 

ComingPerHour = CALCULATE(COUNTROWS('Table1'),FILTER(ALLSELECTED(Table1),'Table1'[Coming]<=MAX('DateTime'[DateTime]) && 'Table1'[Coming]>MAX('DateTime'[DateTime])-TIME(1,0,0) ))

LeavingPerHour = CALCULATE(COUNTROWS('Table1'),FILTER(ALLSELECTED(Table1),'Table1'[Leaving]<=MAX('DateTime'[DateTime]) && 'Table1'[Leaving]>MAX('DateTime'[DateTime])-TIME(1,0,0) ))

From the screenshot, you can see with hour 9/27/2017 1:00 AM - 9/28/2017 2:00 AM, 1 person coming, with hour 9/27/2017 2:00 AM - 9/27/2017 3:00 AM, 2 persons coming. 

Best Regards,
Qiuyun Yu 

[tuomo_kareoja · Accepted solution]

Thanks! This is great, but only solves half of the problem. I would still need to calculate the numbers of patients in the hospital at each hour.

[v-qiuyu-msft · Accepted solution]

Hi @tuomo_kareoja,

You can create a measure below: 

ExistingPerHour = CALCULATE(COUNTROWS('Table1'),FILTER(ALLSELECTED(Table1),'Table1'[Coming]<=MAX('DateTime'[DateTime]) && 'Table1'[Leaving]>MAX('DateTime'[DateTime]))) 

See attached updated pbix file. 

Best Regards,
Qiuyun Yu 

[bstregiel]

What changes would I need to make if I were counting by months instead of hours?

[tuomo_kareoja]

Excellent! Thank you very much!

[Anonymous]

Hi,

I have found what you have acomplished here useful to work out when my members start and leave but when I apply this model I don't manage to get an accumulation of my existing members over time.  In this instance I'm looking at 6+ months. And in other cases i will need to apply it over a period of years.  How do I adjust the measures in the 'existing measure' to include an accumulation of members over a period of time?

Fantastic model, thank you:)

Thank you,

Maria