calculation across rows

[heramiah]

Hi, thanks in advance. First time on this forum.

In power bi i need to perform a calculation to work out date/time difference between Date1 and Date2. This calculation is to be ONLY performed where the ID number is a match to previous rows ID number. See example below. 

I am assuming i need to add a new measure (new column). I need help with the calculation/synntax

Regards,

Hera

[ankitpatira]

@heramiah First go to power bi desktop query editor and under Add Column tab click Add Index column and add index. Then Close&Apply and under Modelling tab create calculated column as below,

Date Difference =
'tableName'[date2Column] - IF(
'tableName'[Index] = 0,
'tableName'[date2Column],
LOOKUPVALUE(
'tableName'[date1Column],
'tableName'[Index],
'tableName'[Index]-1)
)

[heramiah]

Many thanks for your reply.

You are basing your calculation on the new Index column. However, i require my Index to be the ID column which is alpha numeric. 

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.

You are very much there - just small tweaks. Please can you help again? Could possibly split the ID column as my actual ID's are Alpha Alpha, followed by numerics and so would just need to slip left by 2.

[v-ljerr-msft]

@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

[procurtstinate]

I'm trying to perform a similar calculation without using dates. I have a dataset based on when a water pump is running or not. I have to calculate difference in the change in the level of water in the tank it is pumping out of between when the pump starts and finishes. The time difference between the two values is unimportant,

I want to add a column in my Power BI report that shows the same as above, a calculation of the SUM(Runtime Level Change) on the last row of data where the pump is running; Runtime level change only calculates IF(PumpRunning = 1).