Datediff for alternate rows

[harshagraj]

Hello All,

How do it get date diff in sec. It should be group by ModelNr and Station Nr. 
Eg: Datediff(17-11-2020 14:46:02,17-11-2020 14:46:11,Sec)

[harshagraj]

Hello @Anonymous  thanks a lot helping. But unfortunately i cant do column because its live connection. And i tried the measur but i am getting wrong values. Please see the attached.

[Anonymous]

Hi @harshagraj ,

According to the official document, it may caused by some limitations...

I did it in three ways after grouping ModelNr and Station Nr columns, please try

ModelNrStation =
[ModelNr ] & [Station Nr]

Measure = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[DepositCarrier_C] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[ModelNrStation] = MAX ( 'Table'[ModelNrStation] )
                && 'Table'[DepositCarrier_C] < MAX ( 'Table'[DepositCarrier_C] )
        )
    ),
    MAX ( 'Table'[PreStopperArrival_C] ),
    SECOND
)

ColumnMethod1 = 
DATEDIFF (
    [DepositCarrier_C],
    MINX (
        FILTER (
            'Table',
            [ModelNr ] = EARLIER ( [ModelNr ] )
                && [Station Nr] = EARLIER ( [Station Nr] )
                && [PreStopperArrival_C] > EARLIER ( [PreStopperArrival_C] )
        ),
        [PreStopperArrival_C]
    ),
    SECOND
)

ColumnMethod2 = 
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[DepositCarrier_C] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ModelNrStation] ),
            [DepositCarrier_C] < EARLIER ( 'Table'[DepositCarrier_C] )
        )
    ),
    [PreStopperArrival_C],
    SECOND
)

My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[amitchandak]

@harshagraj , Try a column like

datediff(minx(filter(table, [ModelNr] =earlier([ModelNr]) && [Station Nr] =earlier([Station Nr]) && [prestoparrivalC] > earlier([prestoparrivalC])),[prestoparrivalC]),[prestoparrivalC] , second)

[harshagraj]

Hello thankyou so much for the reply. I tried inserting a Measure and I am on Direct Query mode. I am getting below error. I am not getting a column after Earlier. Am i missing something? Please help