Record Changes

Hi Sir_night,

It's obviously there is an order of "Date" for a "Ref" in your scenario. So let's sort the data first, then add an index to keep the orders. Finally, we just need to compare "Status" between "index" and "index + 1".

1. Open "Query Editor", do "Sort Ascending" of "Ref", then do "Sort Ascending" of "Date". There will be two arrows. (blue rectangle). Please don't break the order of ordering. "Ref" first, "Date" second.

2. Add an index.

3. Add a calculated column.

Status changed =
VAR NextStatus =
    CALCULATE (
        VALUES ( Table1[Status] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Ref] = EARLIER ( Table1[Ref] )
                && 'Table1'[Index]
                    = EARLIER ( Table1[Index] ) - 1
        )
    )
RETURN
    IF ( ISBLANK ( NextStatus ), 0, IF ( 'Table1'[Status] = NextStatus, 0, 1 ) )

Best Regards!

Dale

Sir_night

Hi David,

It would be a little complicated.

Method 1: Add a new column, tag the previous record only. (depend on [Status changed])

Status changed =
VAR NextStatus =
    CALCULATE (
        VALUES ( Table1[Status] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Ref] = EARLIER ( Table1[Ref] )
                && 'Table1'[Index]
                    = EARLIER ( Table1[Index] ) - 1
        )
    )
RETURN
    IF ( ISBLANK ( NextStatus ), 0, IF ( 'Table1'[Status] = NextStatus, 0, 1 ) )

Method 2: there are four statuses here. (independant)

0   isn't changed and isn't previous

1   changed and isn't previous

2   isn't changed and previous

3   changed and previous 

StatusFlag =
VAR LastStatus =
    CALCULATE (
        VALUES ( Table1[Status] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Ref] = EARLIER ( Table1[Ref] )
                && 'Table1'[Index]
                    = EARLIER ( Table1[Index] ) - 1
        )
    )
VAR NextStatus =
    CALCULATE (
        VALUES ( Table1[Status] ),
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Ref] = EARLIER ( Table1[Ref] )
                && 'Table1'[Index]
                    = EARLIER ( Table1[Index] ) + 1
        )
    )
RETURN
    IF (
        ISBLANK ( LastStatus )
            && 'Table1'[Status] = NextStatus,
        0,
        IF (
            ISBLANK ( LastStatus )
                && 'Table1'[Status] <> NextStatus,
            2,
            IF (
                ISBLANK ( NextStatus )
                    && 'Table1'[Status] = lastStatus,
                0,
                IF (
                    ISBLANK ( NextStatus )
                        && 'Table1'[Status] <> lastStatus,
                    1,
                    IF (
                        'Table1'[Status] = NextStatus
                            && 'Table1'[Status] = lastStatus,
                        0,
                        IF (
                            'Table1'[Status] = lastStatus
                                && 'Table1'[Status] <> NextStatus,
                            2,
                            IF ( 'Table1'[Status] <> lastStatus && 'Table1'[Status] <> NextStatus, 3 )
                        )
                    )
                )
            )
        )
    )

Best Regards!

Dale