Forum Discussion

harshagraj's avatar
harshagraj
Icon for Post Partisan rankPost Partisan
5 years ago

Datediff for alternate rows

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)

 

4 Replies

  • harshagraj , Try a column like

     

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

    • harshagraj's avatar
      harshagraj
      Icon for Post Partisan rankPost Partisan

      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 

  • Anonymous's avatar
    Anonymous
    Not applicable

    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

  • 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.