Peak Usage based on users' login and logout

[Anonymous]

Hi,

Since the replies I got from my last post help me out so much, I decided to give the forum another try.

So, this is an example of my table:

Username	Logged in	Logged out
Alan	01/01/2020 01:40:03	01/01/2020 02:04:54
Fabiano	01/01/2020 01:54:08	01/01/2020 02:03:23
Anderson	01/01/2020 02:12:26	01/01/2020 02:37:53

I need to create a report of peak usage of one of our systems (based on the distinct users and these two entries).

I was initially thinking on analysing it by the hour (like from 12:00 to 13:00, my peak of logged users was X, from 13:00 to 14:00 it was Y). Using these results I would be able to determine peaks by Month, Year and this kind of stuff.

If it was only based on one of these two entries, just login or just logout, I would know how to do it, but not based on both.

PS: Which user itself doesn't matter, I'm just interested in the peaks (number of users logged).

Anyway, thanks in advance just for reading this. Any help at all would be welcome, even just tips.

Regards

[Smauro]

Alright, it's been long since I've played around with dax, but here goes.

@Anonymous, 3 steps:

1) Working DateTime table from Jan1 to today:

let
    Begin = #datetime(2020, 1, 1, 0, 0, 0),
    NowT = let d = DateTime.LocalNow() in #datetime(Date.Year(d), Date.Month(d), Date.Day(d), Time.Hour(d), Time.Minute(d), 0),
    Dur = Duration.TotalMinutes( NowT - Begin ),
    Dates = List.DateTimes(Begin, Dur,  #duration(0, 0, 1, 0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateTime"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateTime", type datetime}}),
    #"Inserted Start of Hour" = Table.AddColumn(#"Changed Type", "Start of Hour", each Time.StartOfHour([DateTime]), type datetime),
    #"Inserted Start of Day" = Table.AddColumn(#"Inserted Start of Hour", "Start of Day", each Date.StartOfDay([Start of Hour]), type date),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Start of Day", "Start of Week", each Date.StartOfWeek([Start of Day], 1), type date),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Start of Week", "Start of Month", each Date.StartOfMonth([Start of Day]), type date)
in
    #"Inserted Start of Month"

2) Get log times to start of minute:

    #"Calculated Start of Minute" = Table.TransformColumns(PreviousStep,{{"Logged in", (current) => #datetime(Date.Year(current), Date.Month(current), Date.Day(current), Time.Hour(current), Time.Minute(current), 0), type datetime}, {"Logged out", (current) => #datetime(Date.Year(current), Date.Month(current), Date.Day(current), Time.Hour(current), Time.Minute(current), 0), type datetime}})
in
    #"Calculated Start of Minute"

where PreviousStep is your previous step's name.

3) DAX Measure to dynamically calculate peak of users in any given time duration:

MaxUserLogged = 
MAXX (
    SUMMARIZE (
        GENERATE (
            SUMMARIZE (
                'usrlog',
                'usrlog'[Logged in],
                'usrlog'[Logged out],
                "Rows", COUNTROWS ( 'usrlog' )
            ),
            CALCULATETABLE (
                VALUES ( 'DateHour'[DateTime] ),
                FILTER (
                    'DateHour',
                    [DateTime] >= 'usrlog'[Logged in]
                        && 'DateHour'[DateTime] < 'usrlog'[Logged out]
                )
            )
        ),
        ''[DateTime],
        "R2", COUNTROWS ( 'usrlog' )
    ),
    [R2]
)

Assuming here that your DateTime table is called 'DateHour' and 'DateHour'[DateTime] is your key. Also that your log table is called 'usrlog'.


And it should work.

Dummy data:

Username	Logged in	Logged out
Alan	01/01/2020 01:40:00	01/01/2020 02:04:00
Fabiano	01/01/2020 01:54:00	01/01/2020 02:03:00
Anderson	01/01/2020 02:03:00	01/01/2020 02:37:00
Fakeq	01/01/2020 02:00:00	01/01/2020 02:37:00
Qefak	01/01/2020 02:00:00	01/01/2020 02:37:00
Anderson	01/01/2020 04:03:00	01/01/2020 04:37:00

Cheers

[V-pazhen-msft]

@Anonymous 

Not sure what exact output you want, but you can set both log in and log out conditions.

To compare with time, you need to change the column type from Date/Time to Time at the top ribbon. 

Column = 
IF([Logged in]>TIME(12,00,00)&&[Logged out]<=TIME(13,00,00),"X",
IF([Logged in]>TIME(13,00,00)&&[Logged out]<=TIME(14,00,00),"Y"))

Then you can count X or Y to get the number of users within each time range.

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Yeah, I understand the logic, but is this accounting login sessions superiors than 1h (for example: Alan logged in at 13:36 and logged out at 15:45)?

Although, this give me an Idea. I'm thinking of using "Milestones" collumns using this logic and check if the user is Logged during these periods, like using a querry and checking who is online by that time, I'll need a collumn for each of these checks, but it's ok.

If you have any suggestions, cuz this is gonna be Ugly. hahaha

Thanks