calculation across rows

heramiah

---

As shown in the example - where the same ID is repeated - require a calculated column to show the date difference. In other words, i am trying to work out if a person visits more than once - what the gap between their visits is in Hours.

If an ID is only present once - then no calculation. The report will already be sorted in ascending order of ID and dates.

---

In this scenario, you can firstly add an index column as ankitpatira mentioned above, then use VAR function to store the current Date1 value, and use SUMX function to calculate the date difference against the previous Date2(using index) when the same ID is repeated. See my sample below.

I assume you have table called MyTestTable like below.

1. Add an index column.

2. Use the formula below to create a calculate column to show the date difference accordingly.

Diff in hours = 
VAR vId = MyTestTable[ID]
VAR vIdex = MyTestTable[Index]
VAR vDate1 = MyTestTable[DATE1]
RETURN
    IF (
        COUNTROWS (
            CALCULATETABLE (
                MyTestTable,
                FILTER (
                    ALL ( MyTestTable ),
                    MyTestTable[ID] = vId
                        && MyTestTable[Index]
                        = vIdex - 1
                )
            )
        ) > 0,
        SUMX (
            FILTER (
                ALL ( MyTestTable ),
                MyTestTable[ID] = vId
                    && MyTestTable[Index]
                    = vIdex - 1
            ),
            DATEDIFF ( MyTestTable[DATE2], vDate1, HOUR )
        )
    )

Regards