Compare text value row data in PBI

[klili]

I have a dataset that has been built that includes version history. Some items have multiple versions, while newer ones only have a single row. The data get refreshed weekly. 

I am wanting to compare the last 2 dates for each item (if there are multiple entries) to see if a value changed. 

I can't share the actual data but it looks something like this:

ID	DATE	TEXT
001	1/12/2022	A
001	1/13/2022	A
001	1/15/2022	B
002	1/12/2022	C
003	1/12/2022	A
003	1/13/2022	B
003	1/13/2022	C

I found this post : https://community.powerbi.com/t5/Desktop/Difference-between-two-rows

which when I was testing with a single ID worked, but I ran into issues with the multiple IDs in a table. 

Conceptually I know I need something along the lines of if the ID is the same, using the last 2 dates, compare the TEXT field. 

Thanks in advance for your help!

[klili · Accepted solution]

Managed to solve it by creating a Partition and geneating an index column for each ID, the expanding the partition, creating a calculation column that increases the existing index by 1, then merging on the index columns. 

[parry2k]

@klili I'm just asking if the result based on the sample data is correct or not and if you had simply confirmed it, I would have provided you the solution but rather I'm getting all sorts of replies. I hope someone else can help, although I already created the solution I'm having a hard time getting a simple answer. Thanks!!

[klili]

What you posted simply wasn't clear to me. More detail as to "is this the result you are looking for" would have helped. because yes, what you sent was correct. I am sorry that a couple replies back and forth is not something you are willing to do when I was confused. I hope you have more patience with others!

[parry2k]

@klili it is your data, what is not clear? It is taking the latest two records of each ids and then comparing the text, not sure what is not clear.

ID 1 - latest two records, the text doesn't match

ID 2 - it has only one record, so it shows matched

ID 3 - it took the latest two records,  text doesn't match

[klili]

how did you do it? I cannot confirm that what you did works with my data without being able to apply the results to my data. A screen shot of a table does not confirm that your solution works. Yes, those results look correct. I know what I am asking for is possible - I am asking for how to do it. 

[parry2k]

@klili the result that I showed is not what you are looking for? I'm confused. Those are the ids, showing the last two records matched or not. I'm not sure about your reply.

[klili]

The screen shot wasn't really very clear. Can you include some more detail so I can see if it solves my question?

[parry2k]

@klili is this something you are looking for?. I added a record for id 4 to test a corner case:

[klili]

No, I can add an index to the entire table, but that doesn't help when I need to compare rows with the same ID.

[truptis]

Hi @klili ,

If you want to find the duplicate enteries then use the below code:
IF_Duplicate_count = IF(CALCULATE(COUNT(tablename[ID]),FILTER(tablename,tablename[ID]=EARLIER(tablename[ID])))>1,1,0)

If this IF_Duplicate_count > 1, that means we have duplicate versions.

You can then check the latest dates for the rows having IF_Duplicate_count >1 

you can use MAX(date) function to check the latest date for each ID where IF_Duplicate_count > 1

For this, create a column:

Max Date =
CALCULATE (
MAX ( tablename[Date] ),
ALLEXCEPT ( tablename, tablename[ID], filter(tablename,tablename[IF_Duplicate_count] >1)
)

Please mark this as a solution if this helps you. Thank you. 

[klili]

Thanks @truptis  I am not looking for duplicates, I am looking for changes. As I mentioned in my initial question, i need to know if the most recent 2 dates have a TEXT value that has changed. Thanks for the reply!