Count number of logins in an hour per day

[eugee123]

Hi, I have a bunch of login and logout dates and time (raw data). I would like to trend the number of logins per hour (eg, 1am to 2am), per day (was thinking to add this as a slicer to filter the dates).

I have created 2 DAX formulas,

1 to create a new table based on hours

1) 

Table = GENERATESERIES(HOUR(MIN('52114L3M1M1'[Login Time])), HOUR(MAX('52114L3M1M1'[Logout Time])))

The other to count the number of logins at each hour

2) 

Operator Log On = CALCULATE(COUNTROWS('52114L3M1M1'), FILTER(ALL('52114L3M1M1'), HOUR('52114L3M1M1'[Login Time]) <= 'Table'[Time (HR)] && HOUR('52114L3M1M1'[Logout Time]) >='Table'[Time (HR)]))

However, with this, I am unable to slice the data based on various dates, as it just adds up the number of logins at each hour for all the dates in my dataset. Could someone kindly advise how to create a relationship between my 2 tables such that i can slice the data based on various dates? Thanks

[v-jayw-msft]

Hi @eugee123 ,

Has the porblem be solved?

Best Regards,

Jay

[eugee123]

Thanks! Can i get some help on how to include the day in the first formula?

[lbendlin]

You're on the right track but there are some caveats.

your formula for 1)  doesn't work when the login hour is later than the logout hour.  You must include the day in your consideration.

GenerateSeries is the right approach though. For each visual row you need to generate a series of hours covered by that row (might be just one hour, but the idea is the same).  Then for EACH login you need to generate the series of hours covered by the interval the user was logged in. Then you need to take ALL these intervals and INTERSECT them with your visuals hours range, and count the matching rows. That will tell you how many users were logged in during that visual row interval.

There are many more caveats but this should get you going.

[eugee123]

Thanks! Can i get some help on how to include the day in the first formula?

[lbendlin]

Table = GENERATESERIES(MIN('52114L3M1M1'[Login Date])+INT(MIN('52114L3M1M1'[Login Time])*24),MAX('52114L3M1M1'[Logout Date]) +INT(MAX('52114L3M1M1'[Logout Time])*24))

[eugee123]

Thanks, but this only results in a column with all the dates, and the timings are only 00:00 hours. I require the timings from 0000 hours to 2300hours. Eventually I hope to be able to get this graph to trend the logins per hour per day. 

[lbendlin]

Yes, my bad. You need to first add Date and time, then multiply the sum by 24 and then take its integer value.