Forum Discussion

motzplo's avatar
motzplo
Regular Visitor
7 years ago
Solved

Timediff between rows depending on index

Hello all,   I have the following problem and I hope some of you can help me. I would like to calculate the time difference between two rows, depending on the ID. Time difference between two rows o...
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi motzplo,

     

    Try this formula, please.

    Column =
VAR currentID = [ID]
VAR currentIndex = [Index]
VAR nextDatetime =
    CALCULATE (
        MIN ( Table1[Datetime] ),
        FILTER (
            'Table1',
            'Table1'[ID] = currentID
                && 'Table1'[Index]
                = currentIndex + 1
        )
    )
RETURN
    DATEDIFF ( [Datetime], nextDatetime, MINUTE )

    Timediff_between_rows_depending_on_index

     

    Best Regards,

    Dale

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

Try this calculated column formula

 

=CALCULATE(MIN(Data[Datetime]),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Datetime]>EARLIER(Data[Datetime])))-[Datetime]

 

Hope this helps.