Is there a way to check if a code has changed in a column filtered by , next date

[Pandadev]

I have a table consisting of 3 columns and 3 million rows , looking to find a way to highlight when a code changes based on matching ID , based on change from next previous date. example below

ID            / Date           / Code

dd2929   24/11/2020    CC  flag row as code changed from previous next date based matching ID

dd2929  23/11/2020     BB

dd2929  22/11/2020     BB  flag row as code changed from previous next date based matching ID

dd2929  19/11/2020    AA

dd2929  17/11/2020    AA

dd2929  15/11/2020    AA

dd2929   14/11/2020    AA

dd2929  12/11/2020    AA

dd2929 10/11/2020    AA

dd2930   24/11/2020    XX   

dd2930  23/11/2020     XX

dd2930  22/11/2020     XX    

dd2930  21/11/2020    XX  flag row as code changed from previous next date based matching ID

dd2930  19/11/2020    YY

dd2930  17/11/2020    YY

dd2930  15/11/2020    YY

dd2930  14/11/2020    FF  flag row as code changed from previous next date based matching ID

dd2930 10/11/2020    YY

[mahoneypat]

Since you have 3 million rows, performance could be an issue.  Please also try this expression and let me know which is faster.

Code Change 2 =
VAR thiscode = Codes[Code]
VAR thisdate = Codes[Date]
VAR prevcode =
    CALCULATE (
        LASTNONBLANKVALUE (
            Codes[Date],
            MIN ( Codes[Code] )
        ),
        ALLEXCEPT (
            codes,
            Codes[ID]
        ),
        Codes[Date] < thisdate
    )
RETURN
    IF (
        ISBLANK ( prevcode ),
        BLANK (),
        IF (
            prevcode <> thiscode,
            "Y"
        )
    )

Regards,

Pat

[Pandadev]

i get this error when adding it to dax , The following syntax error occurred during parsing: Invalid token, Line 3, Offset 112, ].

Is there a solution that works in power query

[mahoneypat]

This is much better done on the DAX side.  Here is another column expression that should work.

Code Change =
VAR thiscode = Codes[Code]
VAR thisdate = Codes[Date]
VAR prevdate =
    CALCULATE (
        MAX ( Codes[Date] ),
        ALLEXCEPT (
            Codes,
            Codes[ID]
        ),
        Codes[Date] < thisdate
    )
VAR prevcode =
    CALCULATE (
        MIN ( Codes[Code] ),
        ALLEXCEPT (
            Codes,
            Codes[ID]
        ),
        Codes[Date] = prevdate
    )
RETURN
    IF (
        ISBLANK ( prevdate ),
        BLANK (),
        IF (
            prevcode <> thiscode,
            "Y"
        )
    )

Regards,

Pat

[Pandadev]

Thanks , that code worked perfect , is it possible to get it work with date time formatted dates as I forgot the dates also have times , as some dates have 3 or 4 rows of data logged

[mahoneypat]

Yes.  It should work with DateTime instead of Date.  Which one did you end up using?  Did you compare calculation times?

Regards,

Pat

[Pandadev]

When I try to run it on the date time column , I get an out of memory errror , please try again when you may have more resources

[amitchandak]

@Pandadev ,

Create a new column like

new column =
var _min= minx(filter(Table, [ID] =earlier([ID]) && [Date] > earlier([Date]) ),[Date])
return
if([code]= minx(filter(Table, [ID] =earlier([ID]) && [Date]= _min]) ,[code]),blank(),1)