Substract previous row from current row (different columns)

[AnnaR]

Hello,

I'm quite new with PWBI calculations and I'm wondering if it's possible to substract a value from a previous row to the current row when those are in different columns. Moreover, the calculation should "re-start" for each Route ID.

Here a screenshot to ilustrate the problem I'm handeling:

The new Calculated Field (or Measure) is Time Between Visits (n) = StartDate (n) - FinishDate (n-1), but for each Route ID "re-starts" the calculation, so Time Between Visits should be 0 when Route ID (n) <> Route ID (n-1).

Note: Would you do it as a Calculated Field or a Measure? Is it something to better handle on Power Query Editor?

Thanks in advance for your help,

Anna

[v-joesh-msft · Accepted solution]

Hi @AnnaR ，

You can create a calculated column like this to meet your needs:

Time Between Visits (n)-cc = 
IF (
    MINX (
        FILTER ( 'Table', 'Table'[ROUTE ID] = EARLIER ( 'Table'[ROUTE ID] ) ),
        'Table'[Index]
    ) = 'Table'[Index],
    0,
    'Table'[STARTDATE]
        - MAXX (
            FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 ),
            'Table'[FINISHDATE]
        )
)

You can also create a measure as follows:

Time Between Visits (n) -ms = 
VAR lastindex =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        FILTER (
            ALLSELECTED ( 'Table'[Index] ),
            'Table'[Index] < MAX ( 'Table'[Index] )
        )
    )
VAR Time_Between_Visits =
    IF (
        lastindex <> BLANK (),
        CALCULATE ( MAX ( 'Table'[STARTDATE] ) )
            - CALCULATE (
                MAX ( 'Table'[FINISHDATE] ),
                FILTER ( ALLSELECTED ( 'Table'[Index] ), 'Table'[Index] = lastindex )
            ),
        0
    )
RETURN
    Time_Between_Visits

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnnaR]

It works! Thank you so much for the help @v-joesh-msft 

---

[amitchandak]

Check 

https://community.powerbi.com/t5/Desktop/DAX-Subtract-previous-row-from-current-row/td-p/570742

https://community.powerbi.com/t5/Desktop/Need-a-column-for-subtraction-of-Previous-row-for-same-Item...