Forum Discussion

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

date/time difference between 2 columns if they have the same reference

Hi   I want to calculate the number of HH:MM between 2 columns - both have the same reference however the inbound has 1 and outbound has 0 and same reg too.   e.g.   Bound    Reg            Tim...
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi AI14 

    Not sure if i fully get you, supposing your data is like:

     

    you can plot a table with the ref column and a measure like:

    Duration = 
VAR _reg = MAX(TableName[Reg])
VAR _time1 =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[Reg] = _reg
    ),
    TableName[Time]
)
VAR _time2 =
MINX(
    FILTER(
        ALL(TableName),
        TableName[Reg] = _reg
    ),
    TableName[Time]
)
RETURN
FORMAT(_time2 - _time1, "h:mm")

     

    it worked like:

     

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

hi AI14 

Not sure if i fully get you, supposing your data is like:

 

you can plot a table with the ref column and a measure like:

Duration = 
VAR _reg = MAX(TableName[Reg])
VAR _time1 =
MAXX(
    FILTER(
        ALL(TableName),
        TableName[Reg] = _reg
    ),
    TableName[Time]
)
VAR _time2 =
MINX(
    FILTER(
        ALL(TableName),
        TableName[Reg] = _reg
    ),
    TableName[Time]
)
RETURN
FORMAT(_time2 - _time1, "h:mm")

 

it worked like:

 

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

    Thank you it worked!