Forum Discussion
Comparing Changes from Current Report and Last Report with Text Values
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 |
2 Replies
- parry2k
Super User
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
