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. DateNDCStatus6/14/202400074-6215-13Removed6/14/202400074-7126-13Removed6/14/202400169-1833-11No Change6/14/202400169-1834-11No Change7/29/202400169-1833-11No Change7/29/202400169-1834-11No Change7/29/202400597-0375-97New7/29/202400597-0400-89New 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] ) )RETURNIF (COUNTX(FILTER('Table',[NDC] = EARLIER([NDC]) && [Date]=PREVD), [NDC])=0, "New", "No Change")Any help is appreciated.Thank you!

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

Frequent Visitor

2 years ago

Solved

Quick comparison of values on same table

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!

Need Help

MattAllington
2 years ago
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.

Forum Discussion

Quick comparison of values on same table

3 Replies

Related content

Comparison Chart

BI Tools Comparison

Dynamic comparison of values

Comparison Table

Table Value Comparison