Comparing Changes from Current Report and Last Report with Text Values

[Anonymous]

Hello. I have a report which lists items on backorder each week. Each week is determined by a date column, and I use a relative date column to determine Current Report (Value of 0), and Previous Report (Value of -1), as the report days are not consistent. Items on backorder are notated in a "Comment" column, as other comments exist that are filtered out.

What I am looking for is to create two tables: One which lists items that have Comment "Backorder" for the Previous Report (-1) but do not have Comment "Backorder" for the Current Report (0), and second table that lists items that have Comment "Backorder" for the Current Report (0) but not for the Previous Report (-1). The idea is to show which backorder have been added or dropped since the previous report.

The easiest solution seems to be to add a new column with an indicator value for each table, which would allow for easy filtering, however I have been unsuccessful. The table below is how I am seeing what I would like. The Indicator column represents what I am trying to accomplish, and the Result column is there for reference purposes only. Any help is greatly appreciated. Thank you.

Date	Relative Week	Comment	Item	Indicator	Result
4/29/2019	0	In Stock	A123	2	Came In To Stock
4/29/2019	0	Backorder	B456	1	New Backorder
4/29/2019	0	Special Order	C789	0	N/A
4/22/2019	-1	Backorder	A123	2	Came In To Stock
4/22/2019	-1	In Stock	B456	1	New Backorder
4/22/2019	-1	Special Order	C789	0	N/A

[parry2k]

@Anonymous i'm not clear about yur requirement. do you want to see -1 (week) value compare (-2) week? Correct?

How B456 in relative week -1 is "New Backorder", it is in stock 

[parry2k]

@Anonymous you can add a column using following expression and then based on the condition you can get indicator

My Last Week Value = 
VAR maxRelativeWeek = CALCULATE( MAX( Table1[Relative Week] ) ) -1
VAR lastWeekValue = CALCULATE( MAX( Table1[Comment] ), ALLEXCEPT( Table1, Table1[Item] ), Table1[Relative Week] = maxRelativeWeek )
RETURN 
lastWeekValue