cancel
Showing results for
Did you mean:
Helper I

## 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

1 ACCEPTED SOLUTION
Community Support

You may use expression below instead of DATEDIFF.

`` ( [_max] - [_min] ) * 24``
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.
3 REPLIES 3
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])

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://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

Proud to be a Super User!

Helper I

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

Community Support

You may use expression below instead of DATEDIFF.

`` ( [_max] - [_min] ) * 24``
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.

Announcements

#### Happy New Year from Power BI

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