Quick comparison of values on same table

[jhollingworth]

Hello,

I have a table with two columns, "Date" and "NDC". I am trying to figure out a way to determine when an NDC is added or removed based on the previous date. The list is updated randomly, so the previous date could be a day, week, or month prior to. Below is what I am going for, but if another way exists that is better, I am open to it.

Date	NDC	Status
6/14/2024	00074-6215-13	Removed
6/14/2024	00074-7126-13	Removed
6/14/2024	00169-1833-11	No Change
6/14/2024	00169-1834-11	No Change
7/29/2024	00169-1833-11	No Change
7/29/2024	00169-1834-11	No Change
7/29/2024	00597-0375-97	New
7/29/2024	00597-0400-89	New

In this example, the first two NDCs have been removed as they are on 6/14/24, but not on the next date's file 7/29/24. The next two NDCs are on both files, so No Change is the result. Finally, the last two NDCs are on 7/29/2024 but are not on 6/14/24, thus the New status.

I have used the below to figure out the New NDCs, but I am having trouble getting the removed.

New NDC Matrix =

VAR PREVD = OFFSET( -1, ORDERBY('Table'[Date] ) )

RETURN

IF (COUNTX(FILTER('Table',[NDC] = EARLIER([NDC]) && [Date]=PREVD), [NDC])=0, "New", "No Change")

Any help is appreciated.

Thank you!

[MattAllington]

Glad you liked my book 🙂

[MattAllington · Accepted solution]

You might find my recent article useful https://exceleratorbi.com.au/finding-the-date-of-the-next-record-in-power-query/

I was solving a different problem but the structure of the problem is the same, ie how to match records on different rows and capture the dates. My only different advice would be to sort by NDC then Date as the first step. 

[jhollingworth]

I sure will!

I would also ask you to sign your book Supercharge Power BI: Power BI is Better When You Learn To Write DAX, but I have the kindle version. It was great and I use it a lot.

I'll update when I try it out.
Thank you!