Invalid operation: division by zero. - error message even though divide by zero is impossible

[cjeh]

The only divide action on the powerapp page is detailed below, I have filtered the _AppVeh collection to ensure no DateDiff('Lease Start Date','Lease End Date') could result in 0, and I have then added 0.0000001 to the result to  ensure that the denominator of the divide action cannot be zero even if the _AppVeh collection is empty (as datediff must be an integer adding 0.0000001 must result in a non-zero value) yet every time I load the app the Invalid operation: division by zero. - error message appears even though divide by zero is impossible?!!?

Set(_HighCount,CountIf(_AppVeh, 'Latest Distance' > (DateDiff('Lease Start Date', 'Latest Distance Date')/(DateDiff('Lease Start Date','Lease End Date')+0.000001))*Distance))

[Rajkumar_404]

Hi @cjeh 

The DateDiff function returns an integer representing the difference in days between two dates. Adding a very small decimal to an integer will not change the integer value because DateDiff will not return a decimal value. Therefore, adding 0.0000001 to the result of DateDiff will not have the intended effect of preventing a zero denominator.

Instead, you should ensure that the denominator is always greater than zero by adding a larger number that will actually affect the integer result, such as 1. Additionally, you should check that the 'Lease Start Date' is before the 'Lease End Date' to avoid a negative number which could also cause issues.

Set(
_HighCount,
CountIf(
_AppVeh,
'Latest Distance' > (
DateDiff('Lease Start Date', 'Latest Distance Date') / 
(DateDiff('Lease Start Date', 'Lease End Date') + 1)
) * Distance
)
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[WarrenBelz · Accepted solution]

@cjeh ,

Another idea - just replace any zero result with 1 (and use the actual result if not zero)

With(
   {
      _Lease:
      DateDiff(
         'Lease Start Date',
         'Lease End Date'
      )
   },
   Set(
      _HighCount,
      CountIf(
         _AppVeh, 
         'Latest Distance' > 
         (
            DateDiff(
               'Lease Start Date', 
               'Latest Distance Date'
            ) /
            If(
               _Lease > 0,
               Lease,
               1
            )
         ) * Distance
      )
   )
)

[cjeh · Accepted solution]

Thanks Warren and Raj I tried both of these, but still get 'Invalid operation: division by zero'

I couldn't workout what was causing it; the collection _AppVeh is a filtered subset of a Sharepoint list 'Vehicles' which I have filtered to remove any item which could cause a division by zero problem, I tried running your suggestions on the sharepoint list directly as well wihout any luck, I was running the _AppVeh collection at app start and have set the app to only manually run the "Set(_HighCount....." off a manual button but still get the error so in the end I created a calculation column in sharepoint which does create Div-0 error entries, but as I don't need these records in the app it resolves the problem for the specific records in the _AppVeh collection, I would love to know why I couldn't resolve the error though?!

[WarrenBelz]

@cjeh ,

Sorry, does not make a lot of sense to me as in the only division calculation, zero has been tested for and replaced with one if present.

[cjeh]

Warren, On review I had another instance of the query which was also causing the error and your solution does work Thanks