Moving Range Difference in Power Apps

Picture this: you have a collection brimming with data, including a RESULT column. Now, you need to unlock the insights hidden within those results by calculating the difference between consecutive rows.

The goal? To add a shiny new "DIFFERENCE" column that dynamically calculates the difference in "RESULT" values between one row and its predecessor.

While finding the absolute values of these differences seems achievable, the real head-scratcher is how to effectively compare values across rows within a Power Apps collection.

To achieve this you can use the following code:

// Add a row number to your data
ClearCollect(
    Col1,
    ForAll(
        Sequence(CountRows(YourData)),
        Patch(
            Last(
                FirstN(
                    testCol,
                    Value
                )
            ),
            {RowNumber: Value}
        )
    )
);

// calculate difference
ClearCollect(
    testCol2,
    AddColumns(
        testCol,
        Difference,
        If(
            ThisRecord.RowNumber = 1,
            0,
            ThisRecord.Result - Index(
                testCol,
                ThisRecord.RowNumber - 1
            ).Result
        )
    )
)

Here is an image of the resulting collection

Collection with the required Difference Column