Forum Discussion
time Tracking
Hello I have been trying for awhile now to make a Time tracking solution that will display Co workers Time that they worked within a system. I have a Excel sheet that has A Date/Time Column, Also have a column with coworkers names within the same excel sheet. I'm trying to make a virtual clock to show what time everyone was within the system working for tracking purposes?
Hi Piersj1994,
Any other fields exist in your table records that can be used to recognize the user status? Or these records are sorted based on the datetime values as sort order and odd/even number as group?(odd : sign in, even: sign out)
If that is the case, you can use these filed or try to add a new column to check records odd/even number based on current user id and datetime values to return the sign in/out status.
Status = VAR rowCount = COUNTROWS ( FILTER ( 'Table', [UserID] = EARLIER ( 'Table'[UserID] ) && [Datetime] <= EARLIER ( 'Table'[Datetime] ) ) ) RETURN IF ( MOD ( rowCount, 2 ) = 1, "In", "Out" )Then you can extract the hour part from the datetime values and calculate the duration include in current hour period.
If current 'In' and 'Out' status is in the same period, calculation should be the 'Out - In’.
For other scenario, calculation will be split to two parts. The ‘In’ part required to calculate the difference between 'In' to the 'end of current time period'. For the 'Out', you can calculate the difference between previous 'In’ record end period and the current 'Out'.
For 'Out' to 'In', these records should be excluded from calculations because user has out of the work.Regards,
Xiaoxin Sheng
5 Replies
Hi Piersj1994,
I'm not so clear for these fields usage and calculation logic, can you please some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
The End goal Is to be able to see how long the User was logged in for and then how long and when they logged out the Tabluea Pic Included is what is trying to be replicated into Power bi
I'm fairly new into this Power BI Application. I'm trying to use the Time that I have within this Query to make a visual that tracks coworkers and what time they are within the specific enviroment. to try and make a Time In and Time out that will showcase the hours they worked and any breaks in between. I'm just not sure where to start.
Hi Piersj1994,
Any other fields exist in your table records that can be used to recognize the user status? Or these records are sorted based on the datetime values as sort order and odd/even number as group?(odd : sign in, even: sign out)
If that is the case, you can use these filed or try to add a new column to check records odd/even number based on current user id and datetime values to return the sign in/out status.
Status = VAR rowCount = COUNTROWS ( FILTER ( 'Table', [UserID] = EARLIER ( 'Table'[UserID] ) && [Datetime] <= EARLIER ( 'Table'[Datetime] ) ) ) RETURN IF ( MOD ( rowCount, 2 ) = 1, "In", "Out" )Then you can extract the hour part from the datetime values and calculate the duration include in current hour period.
If current 'In' and 'Out' status is in the same period, calculation should be the 'Out - In’.
For other scenario, calculation will be split to two parts. The ‘In’ part required to calculate the difference between 'In' to the 'end of current time period'. For the 'Out', you can calculate the difference between previous 'In’ record end period and the current 'Out'.
For 'Out' to 'In', these records should be excluded from calculations because user has out of the work.Regards,
Xiaoxin Sheng
I have the Log in and out status with the User, Also have the actual time of status and the hour column next to it.
