Count how many users logged in per hour

[PaulSev]

In Power BI Desktop, I want to create a column chart showing number of agents logged in for each hour.

My data table has the following columns. Agent Name, Date, Time, Line Status, Hour (Time rounded to the nearest hour)

• Line Status will change throughout the day; it could be:  Talking, On a Break, On Lunch etc..each change in Line Status is a new record for that agent.
• Outside of the Line Status "Login" and Line Status "Logout" times, the agent is not online.
• Agents login and logout once per day.
• Agents will login\logout at different times depending on shift.

For example

AgentA logs in at 6am, logs out at 2pm

AgentB logs in at 7am, logs out at 3pm

6am - 7am, agent count = 1

7am - 2pm, agent count = 2

2pm - 3pm, agent count = 1

3pm - next agent login, agent count = 0

Therefore I know that 2 agents are logged at say 10am, but my data doesn't show that, I don't have a record for that hour. 

In my efforts to produce a chart I have related my table to an Hour table. A simple list of hours 1-24.

I have only managed to count how many records exist for each hour, not how many people are logged in. So again, I have 0 records for 10am, 1 record for 6am etc...

The only solution I can think of is this:

• Create a day and hour table for every day and hour my agent data covers. Lets say Jan 01 to date.
• Merge the two tables so I have a record for every day and hour, for every agent
• Fill down the columns
• In my chart, filter out the Line Status = "logout"

I feel this is a brute force solution, IF it is even possible, there must be a smarter way.

[Yuvarajand]

Pretty old topic. Curious to understand how different solutions look now.

My approach (Not DAX but Power Query)
- My table has these fields : Session ID, User ID, Login Date time, Log off date time

- Using Power Query to capture login datetime and logoff datetime. Use current date time for users who are still active.

- Decide on the granularity, I will choose hour.

- In Power Query, transform login datetime to start of the hour. Transform logoff datetime to the end of the hour.

- Calculate the duration in hours using 

each Duration.TotalHours([Current Datetime]-[Login Datetime])

- Next explode the columns into the number of duration by adding a column.

= Table.AddColumn(#"Changed Type3", "Active Session Window", each List.DateTimes([Login Datetime],[Active Hours],#duration(0,1,0,0)))

- Next, add column, and extract the hour.

- You can use this column to now cluster the individual rows.

Good luck!

[Phil_Seamark]

HI @PaulSev

Do you have a small sample set of data for your data table?

[PaulSev]

sample data file here https://1drv.ms/f/s!Al0-FJfQdfsNiWMXFH7gM1Ri67p1