Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Pandadev
Post Prodigy
Post Prodigy

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

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

 

 

 

7 REPLIES 7
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Pandadev
Post Prodigy
Post Prodigy

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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
Super User
Super User

@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)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.