cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

How to calculate over hours based on log in / out activity

Hello,

 

Pls. help. I would like to calculate the over hours per day based on Event log data. Implications:

* Any day ex. 01.01.2020 would have multiple logon and off events, therefore it would be great to get the:

 

1)   Logon time  - would be the earliest (MIN) log from Date & Time column (Task Category Logon) for any day

2)   Logoff time - would be the the latest (MAX) from Date & Time column (Task Category Logoff) for any day

3)   9 Hour work day (incl. 1h lunch)

 

The usual working hours are 08:00 to 17:00 (5PM). The hours worked over the weekend (non working days) could be counted as over hours as well.

 

Afterwards, I will add the visuals already per week, month and so on. based on the 1) & 2) as there are too many log dates and time intervals

 

Appreciate your help! Many Thanks!

 

 

Request.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@mspowerbiuser11 

 

You may use expression below instead of DATEDIFF.

 ( [_max] - [_min] ) * 24

 https://community.powerbi.com/t5/Desktop/Calculate-Average-Login-Duration/m-p/236642#M105275

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

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

First of create a date column from date and time and join it date calendar.

Date = Table[Date and Time].Date

I do not see anything like employee id, or multiple entries are for whom. I assumed Employee ID.

Try formula's like these

Max Logout Date = maxx(filter(Table,table[Task Category]="Logoff"),Table[Date and Time])
Min Login Date = minx(filter(Table,table[Task Category]="Logon"),Table[Date and Time])

Hour Diff = AVERAGEX(SUMMARIZE(Table,Date[Date],Table[Employee ID] ,"_min",[Min Login Date],"_max",[Max Logout Date]),datediff([_min],[_max],HOUR))

 

Please of the hour, you can use Minute or second.

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

 
 

Hi amitchandak,

 

Thank you your effort. Unfortunately it would give me a fixed result of 9 hours. I assume it is because of the calulation in the Max logout and Min llogon date which seem to have a fixed timne as well.

 

I basically want to display the over hours of any day considering the min and max logon / logoff time 

 

If you have any other ideas, I would be glad to hear them!

 

Thank you 

 
Highlighted

@mspowerbiuser11 

 

You may use expression below instead of DATEDIFF.

 ( [_max] - [_min] ) * 24

 https://community.powerbi.com/t5/Desktop/Calculate-Average-Login-Duration/m-p/236642#M105275

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors