Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 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 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

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

3 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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