Creating concurrent usage data

[coolioaus]

Hi All,

I'm new to Power BI and trying to get my head around something which is probably relatively simple. We have a system that tracks users signing in and out. This may be once per day or it may be multiple times. What I want to be able to do is identify how many people are signed in at any given point during the day, and also identify the peak periods for sign in.

For example, I can see from this data that User B was the only one signed in from 5.25 > 6.00. From 6.00 User C was also signed in so I need to be able to show a concurrent sign in count of 2. User B then signs out before User A signed in. 

Thanks in advance

[Anonymous]

Hi,

Based on the description, you have solved the problem. If it's convenient, you can share the solution process, which can help others to better solve similar problems.

Best Regards,

Wisdom Wu

[coolioaus]

Thanks for all the responses. I have a working solution and learnt a lot!

[Thejeswar · Accepted solution]

Hi @coolioaus ,

The Solution I would use will go like this

The Below is the dataset I used for this purpose

Username Log In Time Log Out Time

user1	2024-06-11 23:37:35	2024-06-12 00:00:35
user1	2024-06-11 05:44:12	2024-06-11 05:54:12
user1	2024-06-11 02:43:11	2024-06-11 03:16:11
user2	2024-06-11 03:08:04	2024-06-11 03:55:04
user2	2024-06-11 11:11:15	2024-06-11 11:41:15
user2	2024-06-11 09:10:31	2024-06-11 10:56:31
user3	2024-06-11 17:49:07	2024-06-11 18:35:07
user3	2024-06-11 11:07:01	2024-06-11 12:00:01
user3	2024-06-11 20:55:09	2024-06-11 22:32:09
user4	2024-06-11 05:12:14	2024-06-11 06:54:14
user4	2024-06-11 00:32:34	2024-06-11 02:25:34
user4	2024-06-11 12:35:52	2024-06-11 14:17:52
user5	2024-06-11 19:36:17	2024-06-11 20:57:17
user5	2024-06-11 07:51:39	2024-06-11 08:14:39
user5	2024-06-11 04:09:19	2024-06-11 05:40:19

1. Create a TimeTable as below

TimeTable = 
    GENERATESERIES(
        TIME(0,0,0), 
        TIME(23,59,0), 
        TIME(0,15,0)   
        )

2. Loaded the data into Power Query and extracted time as separate columns

You can do this using the Time Dropdown in Power Query Editor

3. Created the below DAX

ConcurrentSignIns = 
VAR CurrentTime = SELECTEDVALUE(TimeTable[Time])
RETURN
COUNTROWS(
FILTER(
'Table',
'Table'[LogInTime] <= CurrentTime &&
'Table'[LogOutTime] >= CurrentTime
)
)

4. You can build a Line Chart, with Time from TimeTable in your X-axis and the ConcurrentSignIns measure on the Y-axis

Regards,

[grazitti_sapna]

Hi @coolioaus ,

To accomplish this in Power BI, we can create a solution using DAX calculations to count concurrent users based on their sign-in and sign-out times. Here’s a step-by-step guide:

1. Load the Data: First, import your data into Power BI. Ensure that the SignIn and SignOut columns are recognized as datetime data types.
2. Create a Time Table: To analyze the data by time intervals (e.g., every 15 minutes or every hour), create a separate time table. This table will allow us to check for users signed in during specific time intervals.
   • Go to the Modeling tab and select New Table.
   • Use the following DAX to create a time table that spans the day (modify the interval as needed):    TimeTable =
     GENERATE(
     CALENDAR(DATE(2024, 6, 11), DATE(2024, 6, 11)),
     VAR StartHour = TIME(0, 0, 0)
     VAR EndHour = TIME(23, 59, 59)
     RETURN GENERATESERIES(StartHour, EndHour, TIME(0, 15, 0))
     )

3. Create a Measure for Concurrent Sign-Ins: This measure will count how many users are signed in during each time interval.

   • Go to the Modeling tab and create a New Measure in your main data table with the following
     DAX:

     ConcurrentSignIns =
     
     VAR CurrentTime = SELECTEDVALUE(TimeTable[Time])
     
     RETURN
     
     COUNTROWS(
     
     FILTER(
     
     'YourDataTable',
     
     'YourDataTable'[SignIn] <= CurrentTime &&
     
     'YourDataTable'[SignOut] >= CurrentTime
     
     )
     
     )

   • Make sure to replace 'YourDataTable' with the name of your actual data table.

4. Visualize the Results: Create a line or area chart to show the count of concurrent users over time.

   1. Add TimeTable[Time] to the X-axis.

   2. Add the ConcurrentSignIns measure to the Y-axis.

5. Identify Peak Periods: To identify peak periods, you could create a new measure to find the maximum concurrent sign-ins in the day:                                                                                                 PeakConcurrentSignIns = MAXX(ALL(TimeTable), [ConcurrentSignIns])

This setup will allow you to see how the number of concurrent sign-ins varies over time and helps identify peak sign-in periods during the day

 

If I have resolved your question, please consider marking my post as a solution. Thank you!

[Thejeswar]

@grazitti_sapna ,

Sorry to say, But this solution you shared looked perfectly like the one copilot creates. If it is so, it undermines the true efforts by other contributors. If my observation is wrong, please ignore

@coolioaus ,

Once the timetable is created, you need to rename the column to time to use in the DAX command

[grazitti_sapna]

hi @Thejeswar ,
Thank you for the feedback! I understand your concern. My goal is always to provide tailored support, and I strive to add unique insights beyond what automated suggestions might offer. I apologize if this response felt similar to Copilot's suggestions—I'm here to add value and make sure the solution truly addresses your specific needs.

[coolioaus]

Thanks, interestingly I also note that the columns have been created with a date of 30/12/1899. The 15 minute increment part is correct?

[Thejeswar]

@coolioaus ,

Change the datatype of the column to Time. The Date shown here appears by default

[coolioaus]

Thanks for the quick response. In working through your solution I've created the TimeTable but receive a warning that "Column 'Time' in 'TimeTable' cannot be found or may not be used in this expression."?