Record Changes

[Sir_night]

I need to identify recorders that have been changed so that they can be flagged, something like below the table.

the data come from a table containing rolling 90 days record

Date	Ref	Status	Status changed
1/02/2017	1	A	0
2/02/2017	1	A	0
3/02/2017	1	B	1
1/02/2017	2	A	0
2/02/2017	2	V	1
3/02/2017	2	A	1

[v-jiascu-msft · Accepted solution]

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

[v-jiascu-msft · Accepted solution]

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 Dale 

is there any way to modify this to mark both the changed record and the prevouse record 

thanks for your help

David

[v-jiascu-msft · Accepted solution]

@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

[vanessafvg]

@Sir_night do you have a status change date, to be honest that would be your best bet is have some sort of indicator when its changed.  

Power bi is not really a database, its a data visualisation tool, so doing this sort of thing is better done at source or with the right data coming into the model.  

In a real world scenario you would need to snapshot the data in order to compare old to new.