Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@Anonymous
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
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
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
@Anonymous
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |