cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
patri0t82
Helper III
Helper III

Calculating 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!

1 ACCEPTED SOLUTION
AlB
Super User III
Super User III

@patri0t82 

 

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 

 

SU18_powerbi_badge

View solution in original post

10 REPLIES 10
AlB
Super User III
Super User III

@patri0t82 

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 

 

SU18_powerbi_badge

You'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

 

patri0t82_0-1610634420965.png

 

AlB
Super User III
Super User III

@patri0t82 

 

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 

 

SU18_powerbi_badge

View solution in original post

Hello AIB, I'm running into memory issues with the DAX formula you've provided, is there any way you'd be able to assist me with a different solution with the same results, whether it be in Power Query or through a measure? 

You are a miracle worker. Thank you so much.

AlB
Super User III
Super User III

@patri0t82 

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 

 

SU18_powerbi_badge

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?

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.

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

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,

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors