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, 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!
Solved! Go to Solution.
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
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
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
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
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.
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
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.
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
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"
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 |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |