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

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

@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
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!

 
 

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 

 

@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
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors