Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hermskies
Frequent Visitor

Calculating hourly occupancy of a medical clinic (based on a start and end datetime)

 

To summize, I would like to create a visual which shows me at an hourly level, how many people are in the medical clinic. This has been done in excel but the requirements are now to have this visual on a power bi report which is connected to our database. 

I have an activity table which shows for each row, details on a person's visit to the medical clinic. 


Activity Table
ID: an identifier for a particular visit
StartDateID: the date at which the person arrives
StartTimeID: the hour at which the person arrives
EndDateID: the date at which the person leaves
EndTimeID: the time at which person leaves

**Other columns containing information about the person: these are things like age group ethnicities etc. I would like to filter by these attributes once we have the chart output. 

 

 

Figure 1. Activity Table (where each row represents one person's visit and their arrival and leave date times, there are other columns which capture more details about the person e.g. age group )

 

ActivityIDEndDateIDStartTimeIDEndDateIDEndTImeIDAge Group
E22673332018102914000020181029170000Elderly
E22674532018102919000020181029220000Non Elderly
E22672182018102911000020181029220000Elderly
E22674732018102920000020181029230000Elderly
E22674632018102920000020181029230000Elderly
E22675782018102914000020181029140000No Elderly
E2264674201810294000020181029180000Elderly
E226741120181029170000201810300Non Elderly
E22674662018102920000020181029200000Elderly
E22674462018102919000020181029190000No Elderly
E22674042018102917000020181029170000Elderly
E22673702018102915000020181029150000Non Elderly
E1971746201608179000020160817100000Elderly
E19717682016081710000020160817110000No Elderly
E19819112016091512000020160915180000Elderly
E19819512016091514000020160915170000Non Elderly
E19819602016091515000020160915200000Elderly
E19820002016091517000020160915180000No Elderly
E19820052016091517000020160915180000Elderly
E19820082016091517000020160915200000Non Elderly
E19820322016091519000020160915200000Elderly

 


The databse is set up in that the four date time related columns in the activity table are joined to date and time dimension tables as per below: 

Figure 2: Activity Relationship table to dimension tables Relationship.PNG

 

So basically, I want to show an output at any given day, how many people are in the medical centre at any hour of the day.

 

E.g. If a person arrives on the 23rd Sep 2018 at 8am and leaves the centre at 4pm in the afternoon, then between 8am and 4pm, the would be counted as 1 in during those hours. Each person from the activity table should be counted like this so the final output is an aggregate of people in the centre at every hour by day. 

 

In the report output, the table should look like this: 

Figure 3. Report Output which sums up the number of people in the medical centre based at each hour of day and date (based on the start Datetime and End DateTime) 

Census DateTimeCalendarDateCensusOccupancy
1/07/2017 0:001/07/201712:00:00 AM29
1/07/2017 1:001/07/20171:00:00 AM26
1/07/2017 2:001/07/20172:00:00 AM22
1/07/2017 3:001/07/20173:00:00 AM20
1/07/2017 4:001/07/20174:00:00 AM19
1/07/2017 5:001/07/20175:00:00 AM20
1/07/2017 6:001/07/20176:00:00 AM19
1/07/2017 7:001/07/20177:00:00 AM14
1/07/2017 8:001/07/20178:00:00 AM17
1/07/2017 9:001/07/20179:00:00 AM20
1/07/2017 10:001/07/201710:00:00 AM25
1/07/2017 11:001/07/201711:00:00 AM27
1/07/2017 12:001/07/201712:00:00 PM33
1/07/2017 13:001/07/20171:00:00 PM31
1/07/2017 14:001/07/20172:00:00 PM35
1/07/2017 15:001/07/20173:00:00 PM35
1/07/2017 16:001/07/20174:00:00 PM31
1/07/2017 17:001/07/20175:00:00 PM34
1/07/2017 18:001/07/20176:00:00 PM33
1/07/2017 19:001/07/20177:00:00 PM39
1/07/2017 20:001/07/20178:00:00 PM42
1/07/2017 21:001/07/20179:00:00 PM45
1/07/2017 22:001/07/201710:00:00 PM37
1/07/2017 23:001/07/201711:00:00 PM39

 

 

This should then allow me to create a visual as per below: 

Figure 4. Chart output where: 

X- Axis = Date field and then time field (from the date and time column in figure 3) . The date component should be able to be filtered. For example I only want to look at a different 7 day range. 

 

Y-Axis: the occupancy of the people in medical centre by hour of day. 

Capture.PNG

Appreciate if you could help me out. I'm new to power bi and this output chart has been done in excel. The requirement for my work is now to migrate a lot of our visuals to power bi dashboard. 

 

I understand that I may have to create some sort of date time dimension table for this visual but a bit stuck as to how to do this (since date and time are separated).

 

Also would appreciate how I would actually create the occupancy measure. In Excel I have used a COUNTIFs formula looking at the Census DateTime column in figure 3 and seeing whether a patient's StartDatetime is less than Census DateTime and EndDateTime is greater than census time.

 

Excel Formula: 

=COUNTIFS(StartDateTime,<=CensusDateTime,EndDateTime,>=CensusDateTime)

 

Let me know if any further clarifications are needed. THanks in advance. 

1 ACCEPTED SOLUTION
Nolock
Resident Rockstar
Resident Rockstar

Hi @hermskies,

there are always more than one solution. I recommend to use only one dim date table which contains dates and hours of a day. This dim table won't be small (which is a disadvantage) but you can use it direct for your visuals.

 

What to do:

  1. Convert StartDate and StartTime into one column StartDateTime, the same for EndDate...
  2. Create a new dim date table from MIN(StartDateTime) to MAX(EndDateTime)
  3. Create a new column in your activity table where every row contains a list of all hours between StartDateTime and EndDateTime - let's call it ListHourly
  4. Expand the list of hours into rows, like in the screenshot below.
  5. Relate ListHourly with your dim date table.
  6. Create a visual where X-axis is from dim date table and values is count of ActivityIDs.

Capture1.PNG

View solution in original post

7 REPLIES 7
Nolock
Resident Rockstar
Resident Rockstar

Hi @hermskies,

there are always more than one solution. I recommend to use only one dim date table which contains dates and hours of a day. This dim table won't be small (which is a disadvantage) but you can use it direct for your visuals.

 

What to do:

  1. Convert StartDate and StartTime into one column StartDateTime, the same for EndDate...
  2. Create a new dim date table from MIN(StartDateTime) to MAX(EndDateTime)
  3. Create a new column in your activity table where every row contains a list of all hours between StartDateTime and EndDateTime - let's call it ListHourly
  4. Expand the list of hours into rows, like in the screenshot below.
  5. Relate ListHourly with your dim date table.
  6. Create a visual where X-axis is from dim date table and values is count of ActivityIDs.

Capture1.PNG

Dear Nolock,

 

I am very new to Power Bi and Power Query, I could not make the ponts 3 working. Could you please have a sample of the power bi file on that points?

 

Best regards,

 

Veasna

Anonymous
Not applicable

@Nolock 
Hey Nolock, I'm trying to reproduce your steps to calculate some occupancy rate for chargingstations, which have 2 sockets.
Only problem with my data is that I don't have a hourly timestamp, rather just a given start and end time.
for example: 

 

Table 1


Start_Time                    End_Time                 Duration           Charge_ID_socket
1-1-2019 14:14:23        2-1-2019 15:14:33   25:00:10             P1234-1
1-1-2019 13:15:15        1-1-2019 23:32:25  10:17:10              P1234-2

.

.

etc.

I have a dim date  table with the min and max date from start and end time, step 3 and 4 are the problem which I cant figure out. Note I'm a newb to Power BI and DAX, so it might be a silly question yet I cant figure it out...

Finaly visualizing the occupancy rate for each charging station + socket individually would be the end goal here. Can you help me out here?

Kind regards!

Hi @Anonymous,

however the old solution is correct, nowadays I would solve it in another way because I know much more about PowerQuery.

The simple solution is shown in the following code incl. sample data. You generate a list of hours between 2 timestamps and then expand the list into rows.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLRMzDXMzIwtFQwMrAyMLcyNFDSUTIyhYsamFoZmFgZGCvFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Start", type datetime}, {"End", type datetime}}),
    HoursBetween = Table.AddColumn(
        ChangedType, 
        "HoursBetweenList", 
        (row) =>
            List.Generate(
                () => Date.StartOfDay(row[Start]) + #duration(0, Time.Hour(row[Start]) + 1, 0, 0), 
                each _ <= row[End],
                each _ + #duration(0, 1, 0, 0) 
            )
    ),
    ExpandList = Table.ExpandListColumn(HoursBetween, "HoursBetweenList")
in
    ExpandList
Anonymous
Not applicable

@Nolock 
Thank you for your reply, however I'm still not getting a hourly list between start and endtime for each session. I must be doing something wrong here. Tried copy paste your code into an empty query which doesn't seem to be working. Any other way of showing me how it's done?
After that I should be able to link my Fact table with this hourly list to my dim date table, right?

Hi @Anonymous,

I've checked the code again and it works as it should. Do you see the same screenshot when you execute the query?

 

Capture.PNG

Anonymous
Not applicable

I got it to work! Now all I need is to have this list alongside my activity/fact table that contains all my sessions, so it will probably quadruple in size but then I can visualize occupancy?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.