In Power BI, you can achieve the goal of viewing only "new" inventory that has shown up for a specific month by using a combination of calculated columns and filters. Here are the steps to do this:
Create a Calculated Column for Monthly Totals: Start by creating a calculated column that calculates the total inventory count for each product (barcode) for each month. You can use the SUMX and FILTER functions to achieve this. Let's assume your table is called "Inventory" and it has columns like "Barcode," "Month," and "Count." Here's an example of how to create the calculated column:
MonthlyTotalCount = SUMX( FILTER( Inventory, Inventory[Month] = EARLIER(Inventory[Month]) && Inventory[Barcode] = EARLIER(Inventory[Barcode]) ), Inventory[Count] )
This calculated column will give you the total count of each product for each month.
Create a Filter for New Inventory: Now, you can create a filter in your visualizations to show only the rows where the "MonthlyTotalCount" is equal to the "Count" for that specific month. This will filter out the rows for products that already existed in previous months. In your visual, you can use a slicer or a filter visual to allow users to select a specific month.
For example, you can use a table or matrix visual, and then set the filter condition as follows:
- Field: Month
- Operator: is
- Value: [Select the desired month from the slicer or filter visual]
This will show only the "new" inventory items for the selected month.
Now, when you select a specific month in your slicer or filter visual, it will display only the products that are considered "new" for that month based on your criteria. The calculated column "MonthlyTotalCount" ensures that you're comparing the count for each product only within the same month, filtering out products that were already present in previous months.
