topic Re: DateDiff giving negative result when the next date-time is less than 24 hrs difference in DAX Commands and Tips

DateDiff giving negative result when the next date-time is less than 24 hrs difference

noannish — Wed, 09 Oct 2024 22:51:01 GMT

This is for a calculated column.

Whenever the [exit time] is the next day, but within 24 hours of [enter time], I get a negative result for minutes.

All other aspects, except this, are working fine.

Minutes =
VAR _NonWorkingDays =
CALCULATE(
COUNTROWS('Calendar'),
'Calendar'[Day Type] <> "Work Day"
)
VAR _WorkTimeElapsed =
DATEDIFF(AuditLog[Enter_Time],AuditLog[Exit_Time], MINUTE) - (_NonWorkingDays* 24 * 60)
RETURN
_WorkTimeElapsed

Re: DateDiff giving negative result when the next date-time is less than 24 hrs difference

SachinNandanwar — Wed, 09 Oct 2024 23:04:02 GMT

I think it is because your exit time is before enter time in the 24 hour period.

Can you post some data ?

Re: DateDiff giving negative result when the next date-time is less than 24 hrs difference

noannish — Wed, 09 Oct 2024 23:25:08 GMT

Minutes	Enter_Time	Exit_Time
-32	2/19/2024 8:47:00 AM	2/20/2024 8:15:00 AM
-153	6/19/2024 11:32:00 AM	6/20/2024 8:59:00 AM
-328	2/19/2024 2:18:00 PM	2/20/2024 8:50:00 AM

Re: DateDiff giving negative result when the next date-time is less than 24 hrs difference

SachinNandanwar — Wed, 09 Oct 2024 23:35:20 GMT

Try something like this in the Return statement

RETURN IF( _WorkTimeElapsed < 0, _WorkTimeElapsed + (24 * 60), _WorkTimeElapsed )

Re: DateDiff giving negative result when the next date-time is less than 24 hrs difference

Anonymous — Tue, 29 Oct 2024 09:31:01 GMT

Hi noannish ,

According to your statement, I think your result is correct.

Working backwards from the results you gave, I think the _NonWorkingDays = 1 and (_NonWorkingDays* 24 * 60) in your code will return 1440.

Since I don't know exactly what your Calendar table looks like, if there is a issue with this part of the result, please check it.

Please make sure the datediff(minutes) between [Enter Time] and [Exit Time].

I think the datediff(minutes) in your sample are all less than 1440, so datediff(minutes) - (_NonWorkingDays* 24 * 60) will return negative result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.