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 tim...
  • 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" ) )

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)

 

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

change the target, returns 1

 

  • 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

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

      hi AI14 

      can you paste your data?

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

        the actual:

        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
        FORMAT(_time2 - _time1, "hh:mm")
         

         

        the Target: (i have this as a measure and a column, both not worked)
        Target = 'Task Rules'[Earliest Start]+'Task Rules'[Duration]+'Task Rules'[Endbefore]+'Task Rules'[Setup]+'Task Rules'[Cleanup]