Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
23 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |