Count machinery splitted stops

[ChrisCollao]

Hi all,

I have this sort of data:

Machine	Start datetime	End datetime
Machine1	2023-12-01 13:00:00	2023-12-02 00:00:00
Machine2	2023-12-02 11:00:00	2023-12-02 15:00:00
Machine1	2023-12-02 00:00:00	2023-12-03 00:00:00
Machine2	2023-12-03 12:00:00	2023-12-03 18:00:00
Machine1	2023-12-03 00:00:00	2023-12-03 14:00:00

The data shows machinary stops splitted by day turns. So, if a detention lenght more than a day, it will be splitted in several rows, but it's only one detention. 

I need to create a column where I can mark a 1 if the detention is new or 0 if it's an old one. The way I can detect if the detention is an old one is by comparing the start time and the end time of the last detention, if they are equal, then the stop is an old one.

How can I do this?

The result for the previous example should be:

Machine	Start datetime	End datetime	New Detention
Machine1	2023-12-01 13:00:00	2023-12-02 00:00:00	1
Machine2	2023-12-02 11:00:00	2023-12-02 15:00:00	1
Machine1	2023-12-02 00:00:00	2023-12-03 00:00:00	0
Machine2	2023-12-03 12:00:00	2023-12-03 18:00:00	1
Machine1	2023-12-03 00:00:00	2023-12-03 14:00:00	0

Thanks in advance. Best regards.

[Fowmy]

@ChrisCollao 
I added a calculated column :

New Detention = 
VAR __Start = Table01[Start datetime]
VAR __PrevEnd = 
    CALCULATE(
        MAX( Table01[End datetime] ),
        ALLEXCEPT( Table01 , Table01[Machine] ),
        Table01[End datetime] <= __Start 
    )
VAR __Result =  INT(__Start <> __PrevEnd)
RETURN
    __Result

[ChrisCollao]

Hi @Fowmy 

Thanks a lot. I tested it with the sample and it works. But, in my model there's more than 300.000 rows and 15 different machines. The column can't be calculated due to "insufficient memory error". Do you know if there's any walkaround we could take?

Thanks a lot.

[ChrisCollao · Accepted solution]

Sorry mom, I used GPT.

I used GPT to see another solution and to not use an iterative DAX formula. This was the result:

ColumnaMarcador = 
VAR MaquinaActual = Detenciones[Maquina] 
VAR FechaHoraInicioActual = Detenciones[FechaHoraInicio] 
VAR FechaHoraFinAnterior = 
MAXX( 
    FILTER( 
          Detenciones, 
          Detenciones[Maquina] = MaquinaActual && 
          Detenciones[FechaHoraFin] < FechaHoraInicioActual 
          ), 
    Detenciones[FechaHoraFin] 
    ) 
RETURN 
    IF( 
      NOT ISBLANK(FechaHoraFinAnterior) && 
      FechaHoraFinAnterior = FechaHoraInicioActual, 
      1, 
      0 
   )

It's amazing that the first GPT solution was pretty similar to the one presented by @Fowmy, but with the instruction of the out of memory error it managed it to make a non interative solution.

If anyone needs the prompt and chat (in spanish), here is: https://chat.openai.com/share/cc6d9a4f-9169-410c-b89c-08f61eeb44cb

Best regards.