topic Comparing Changes from Current Report and Last Report with Text Values in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/683813#M461 <P>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.</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Relative Week</TD><TD>Comment</TD><TD>Item</TD><TD>Indicator</TD><TD>Result</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>In Stock</TD><TD>A123</TD><TD>2</TD><TD>Came In To Stock</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>Backorder</TD><TD>B456</TD><TD>1</TD><TD>New Backorder</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>Special Order</TD><TD>C789</TD><TD>0</TD><TD>N/A</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>Backorder</TD><TD>A123</TD><TD>2</TD><TD>Came In To Stock</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>In Stock</TD><TD>B456</TD><TD>1</TD><TD>New Backorder</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>Special Order</TD><TD>C789</TD><TD>0</TD><TD>N/A</TD></TR></TBODY></TABLE> Sat, 04 May 2019 13:31:21 GMT Anonymous 2019-05-04T13:31:21Z Comparing Changes from Current Report and Last Report with Text Values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/683813#M461 <P>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.</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><P>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.</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>Date</TD><TD>Relative Week</TD><TD>Comment</TD><TD>Item</TD><TD>Indicator</TD><TD>Result</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>In Stock</TD><TD>A123</TD><TD>2</TD><TD>Came In To Stock</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>Backorder</TD><TD>B456</TD><TD>1</TD><TD>New Backorder</TD></TR><TR><TD>4/29/2019</TD><TD>0</TD><TD>Special Order</TD><TD>C789</TD><TD>0</TD><TD>N/A</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>Backorder</TD><TD>A123</TD><TD>2</TD><TD>Came In To Stock</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>In Stock</TD><TD>B456</TD><TD>1</TD><TD>New Backorder</TD></TR><TR><TD>4/22/2019</TD><TD>-1</TD><TD>Special Order</TD><TD>C789</TD><TD>0</TD><TD>N/A</TD></TR></TBODY></TABLE> Sat, 04 May 2019 13:31:21 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/683813#M461 Anonymous 2019-05-04T13:31:21Z Re: Comparing Changes from Current Report and Last Report with Text Values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/684614#M477 <P>Anonymous</LI-USER> i'm not clear about yur requirement. do you want to see -1 (week) value compare (-2) week? Correct?</P> <P>&nbsp;</P> <P>How B456 in relative week -1 is "<SPAN style="display: inline !important; float: none; background-color: #ffffff; color: #000000; font-family: 'SegoeUI','Lato','Helvetica Neue',Helvetica,Arial,sans-serif; font-size: 15px; font-style: normal; font-variant: normal; font-weight: 300; letter-spacing: normal; line-height: initial; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-break: normal; word-spacing: 0px;">New Backorder</SPAN>", it is in stock&nbsp;</P> <P>&nbsp;</P> Mon, 06 May 2019 16:17:05 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/684614#M477 parry2k 2019-05-06T16:17:05Z Re: Comparing Changes from Current Report and Last Report with Text Values https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/684618#M478 <P>Anonymous</LI-USER> you can add a column using following expression and then based on the condition you can get indicator</P> <P>&nbsp;</P> <PRE>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</PRE> Mon, 06 May 2019 16:21:47 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Comparing-Changes-from-Current-Report-and-Last-Report-with-Text/m-p/684618#M478 parry2k 2019-05-06T16:21:47Z