Forum Discussion

AI14's avatar
AI14
Icon for Helper III rankHelper III
3 years ago
Solved

compare text to time

Hi

 

i need to see if my Actual is less than my target but they are in different formats. both are measures however 1 has been formatted into HH:MM so its a text and the other is a normal short time.

 

e.g.

the text time measure is as follows: Actual= Format(example),"hh:mm)  -  result is 02.45

the Target measure might say 03:55 (in short time format)

 

the if formula is not working due to formatting issues :

if(actual<target,1,0)

 

please assist

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi AI14 

    try to use TIMEVALUE function to convert Duration to datetime data type as follows

    Duration =
    VAR _ref =
    MAX ( a[REF] )
    VAR _time1 =
    MAXX ( FILTER ( ALL ( a ), a[REF] = _ref ), [ST] )
    VAR _time2 =
    MINX ( FILTER ( ALL ( a ), a[REF] = _ref ), [ST] )
    RETURN
    TIMEVALUE ( FORMAT ( _time2 - _time1, "hh:mm" ) )

8 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi AI14 

     

    try like:

    Measure = 
IF(
    VALUE([Actual])< [Target], 
    1, 0 
)

     

    verified with such data:

    Actual = FORMAT(TIME(2,45,0), "hh:mm")

Target = TIME(1,55,0)

     

      • AI14's avatar
        AI14
        Icon for Helper III rankHelper III
        3 years ago

        also Tried this but says i cant convert value " of type txt to type date

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi AI14 
    You can use 
    IF ( TIMEVALUE ( actual ) < target, 1, 0 )

    • AI14's avatar
      AI14
      Icon for Helper III rankHelper III
      3 years ago

      Tried this but says i cant convert value " of type txt to type date