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

[mspowerbiuser11]

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!

[amitchandak]

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

[mspowerbiuser11]

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 

[v-chuncz-msft · 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