Dax TIME function bug with Hour = 13

Hi cturle 

This is indeed confusing since, on the face of it, the two time values should be equal 😞

Since Date/Time values are stored as Decimal values , the values are stored approximately, and any equality-related comparisons can return unexpected results.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#datetime-types

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types#accuracy-of-number-type-calculations

In your second example, you can verify that there is a difference in the Decimal values stored by subtracting the two values and formatting in scientific notation for example, giving a difference of approximately -1.1102E-16.

I can't explain in this specific case why the two time values are stored as slightly different values, other than point to the above documentation. Using SELECTEDVALUE to return a value from a (query) table presumably plays a role. Perhaps someone out there can explain the specifics in this case! 🙂

Because of all this, for any equality-related comparisons of Date/Time or Decimal values, I would suggest either rounding the values or testing whether ABS ( difference ) < threshold.

e.g.

DEFINE
    TABLE myMinute = {
        12
    }
    VAR MyHour = 13
    VAR Threshold = 1E-6 -- small threshold
    VAR RoundingMultiple = 1E-6 -- small multiple for rounding

EVALUATE
VAR Time1 =
    TIME ( MyHour, SELECTEDVALUE ( myMinute[Value] ), 0 )
VAR Time2 =
    TIME ( MyHour, 12, 0 )
RETURN
    {
        ( "Hour", MyHour ),
        ( "myMinute", SELECTEDVALUE ( myMinute[Value] ) ),
        ( "check in TIME", Time1 = Time2 ),
        ( "check in TIME threshold", ABS ( Time1 - Time2 ) < Threshold ),
        ( "check in TIME rounded", MROUND ( Time1, RoundingMultiple ) = MROUND ( Time2, RoundingMultiple ) )
    }

Regards