Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Substract previous row from current row (different columns)

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 calcu...
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    7 years ago

    Hi Anonymous ，

    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.