Forum Discussion

motzplo's avatar
motzplo
Regular Visitor
8 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 of a different ID is not possible. The time difference should look like the following picture.

 

 

Thank you in advance.

 

  • 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

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    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
    Icon for Super User rankSuper 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.