Forum Discussion

noannish's avatar
noannish
Frequent Visitor
2 years ago

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

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...
CalculatedColumn
DATEDIFF
DAX
SachinNandanwar's avatar
SachinNandanwar
Icon for Impactful Individual rankImpactful Individual
2 years ago

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

Can you post some data ?

noannish's avatar
noannish
Frequent Visitor
2 years ago
MinutesEnter_TimeExit_Time
-322/19/2024 8:47:00 AM2/20/2024 8:15:00 AM
-1536/19/2024 11:32:00 AM6/20/2024 8:59:00 AM
-3282/19/2024 2:18:00 PM2/20/2024 8:50:00 AM
  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Try something like this in the Return statement

    RETURN IF(
_WorkTimeElapsed < 0,
_WorkTimeElapsed + (24 * 60),
_WorkTimeElapsed 
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.