Calculating subtraction between different columns and rows

[aguas]

Ola pessoal!

Preciso de ajuda: Preciso calcular a diferença entre o final de uma ordem de serviço e o início da próxima, para assim ter o tempo de viagem da equipe:

Estou usando a seguinte fórmula que encontrei aqui no forum:

Tempo de Deslocamento =

VAR Index_1 = Ordens_de_Servico_Executadas[Índice] - 1

VAR CurrentEQP =

CALCULATE (

MAX ( 'Ordens_de_Servico_Executadas'[EQP] );

FILTER ( 'Ordens_de_Servico_Executadas'; 'Ordens_de_Servico_Executadas'[Índice]= Index_1 + 1 )

)

VAR LastEQP =

CALCULATE (

MAX ( 'Ordens_de_Servico_Executadas'[EQP] );

FILTER(Ordens_de_Servico_Executadas; 'Ordens_de_Servico_Executadas'[Índice] = Index_1 )

)

VAR HoraInicio = 'Ordens_de_Servico_Executadas'[Hora Inicio]

VAR HoraFim =

IF (

CurrentEQP = LastEQP;

MAXX ( FILTER ( Ordens_de_Servico_Executadas; Ordens_de_Servico_Executadas[Índice] = Index_1 ); LastEQP )

)

VAR DateDiffSeconds =

DATEDIFF ( HoraFim; HoraInicio; SECOND )

VAR Hours =

TRUNC ( DateDiffSeconds / 3600 )

VAR Minutes =

TRUNC( (DateDiffSeconds - Hours * 3600 ) / 60 )

VAR Seconds =

MOD ( DateDiffSeconds - Hours * 3600; 60 )

VAR Result =

IF (

HoraFim <> BLANK ();

FORMAT ( Hours; "00" ) & ":"

& FORMAT ( Minutes; "00" ) & ":"

& FORMAT ( Seconds; "00" )

)

RETURN

Result

Mas a mesma não está funcionando. Alguem pode me ajudar?

[v-xuding-msft]

Hi @aguas ,

Please reference this to have a try.

Measure = 
VAR NextEndDate =
CALCULATE (
    MIN('Table'[End date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Index] < SELECTEDVALUE ( 'Table'[Index] )
    )
)
VAR DateDiffSeconds =
    DATEDIFF (  MAX ( 'Table'[Start date] ),NextEndDate, SECOND )
VAR Hours =
    TRUNC ( DateDiffSeconds / 3600 )
VAR Minutes =
    TRUNC ( ( DateDiffSeconds - Hours * 3600 ) / 60 )
VAR Seconds =
    MOD ( DateDiffSeconds - Hours * 3600, 60 )
VAR Result =
    IF (
        DateDiffSeconds <> BLANK (),
        FORMAT ( Hours, "00" ) & ":"
            & FORMAT ( Minutes, "00" ) & ":"
            & FORMAT ( Seconds, "00" )
    )
RETURN
    Result

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aguas]

Hello @v-xuding-msft 

The calculation didn't work. I tried to apply it but the column remains blank.

However, I believe that it is due to the fact that there are several teams with different names, but even organizing by team and completion date, and then inserting the index column, it does not work. I can't think of what it might be.

Any idea what it might be?

Thank you!

[amitchandak]

Can you provide a better dataset to check? We need have some id to take reference and do the diff. Mark me with @

[aguas]

hello @amitchandak!

Let me explain better: I have different teams (EQP) with several work orders (IDOSP) to execute, so I would like to know the travel time (end time of a work order minus the start time of the next one) .

I have already tried to sort the columns by team and by the completion date and then add an index column. Even so, the calculation did not work.

Any ideas how to solve this?

Thank you!

[amitchandak]

I did not get this completely. Few questions.

1. Do we need to go across the rows to find rows?

2. Are dates of dd/mm/yyyy detected as a date.

3. If it is in the same row. You need to append the date and time. Like start date and start time and end date and end time and then take a date diff. Is that what can solve your issue.