Forum Discussion
Post PatronCalculating time between two rows of datetime stamps
Hello, I have gate scans being provided to me on a daily basis and I need to calculate time spent on and offsite per person. Each record contains a date stamp of either a 'entry' or 'exit'. It would be really easy if they were on the same line to then just subtract one from the other, but unfortunately the data can't be sent to me that way.
Is there any easy way to subtract one line from the previous line given the name and company match, or to bring the "Exit" and Time stamp up to the previous row, using Power Query or a measure in PBI?
Also, if there's no Exit, the record should return null, or a current time stamp.
https://drive.google.com/file/d/1a9vikrazeb-YqmCNsb1yUkmNdd6bQ8TI/view?usp=sharing
Thanks for your help!
Time on Site (Minutes) V2 = IF ( Table1[Direction] = "Exit", VAR aux_ = CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ) RETURN IF ( NOT ISBLANK ( aux_ ), 24 * 60 * ( Table1[Field Date Time] - aux_ ) ) )Time on Site (HH:MM) V2 = IF ( Table1[Direction] = "Exit", VAR aux_ = CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ) RETURN IF ( NOT ISBLANK ( aux_ ), FORMAT ( Table1[Field Date Time] - aux_, "HH:MM" ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
10 Replies
- AlB
Community Champion
Time on Site (HH:MM) = IF ( Table1[Direction] = "Exit", FORMAT ( Table1[Field Date Time] - CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ), "HH:MM" ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
patri0t82You're so good at this. I'm encountering one final problem that appears to be significant.
Our first source file is from the 7th and contains many "Exit"s, but no corresponding prior "Entry"s. What happens is, it maxes out the minutes column and doesn't make sense. Is there away to show if there's no Entry then point to the beginning of the day of the Exit?
You can see below that some of the minutes are as high as 63,652,980
- AlB
Time on Site (Minutes) V2 = IF ( Table1[Direction] = "Exit", VAR aux_ = CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ) RETURN IF ( NOT ISBLANK ( aux_ ), 24 * 60 * ( Table1[Field Date Time] - aux_ ) ) )Time on Site (HH:MM) V2 = IF ( Table1[Direction] = "Exit", VAR aux_ = CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ) RETURN IF ( NOT ISBLANK ( aux_ ), FORMAT ( Table1[Field Date Time] - aux_, "HH:MM" ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- AlB
Hi patri0t82
Try this for a calculated column:
Time on Site (Minutes) = IF ( Table1[Direction] = "Exit", 24 * 60 * ( Table1[Field Date Time] - CALCULATE ( MAX ( Table1[Field Date Time] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[Field Date Time] < EARLIER ( Table1[Field Date Time] ) ) ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- patri0t82
Thank you for your quick response..
I've tried what you suggested but it returns an error, if you wouldn't mind helping me understand that would be greatly appreciated. Two field names are now changed to "FieldDateTime" and "Transit Direction"
Time on Site (Minutes) = IF ( Table1[Transit Direction] = "Exit", 24 * 60 * ( Table1[FieldDateTime] - CALCULATE ( MAX ( Table1[FieldDateTime] ), ALLEXCEPT ( Table1, Table1[Company], Table1[Name] ), Table1[FieldDateTime] < EARLIER ( Table1[FieldDateTime] ) ) ) )I receive the error: A single value for column 'FieldDateTime' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.Also, it would be nice to have the exit time stamp visible for those who would like to see it - does your calculation allow for that?Thank you again,
- AlB
Works on my side. See it all at work in the attached file.
I do not understand whta you mean by this. An example would help:
Also, it would be nice to have the exit time stamp visible for those who would like to see it - does your calculation allow for that?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
- patri0t82
Thank you again, you're very quick. I see how your sample file is working - thanks for including it. I guess what I was asking before is having both time stamps available beside each other, Entry and Exit so the information is available if people are not confident in the calculation.
Would you mind explaining to me what happens in an instance where Person A has no entry or exit scan, due to the cutoff time to the file?
For example, if the report is run at 8:30 this morning, it needs to show that Person A is currently on site. Would those hours be included as of the refresh time?
- patri0t82
I should add one thing, - this solution is working well I believe, is there a way to return the values as HH:MM instead of just minutes? I think that would be ideal for our users.