Filtering a pivot table report using a calculated column

[LBala]

Hi;

I have a sales report linked with an inventory table (current quantity available by item). Here is a picture of the diagram view:

In the "Inventory" table I have a calculated column to indicate if the quantity on-hand of an item (QtyOH) is positive or negative. The calculated column is named "OH_Qty_Status" (on-hand quantity status). The calculated column formula is: =IF((Inventory[QtyOH]) < 0, "Negative", "Positive")

I have created a pivot table report with the filters "State" and "OH_Qty_Status" on top. I am filtering the state "TX" and "OH_Qty_Status" equal to "Negative". As I wanted, only the items with less than zero quantity are showing.
Here is a picture of the pivot table report:

I have also created a calculated field named "QtySold_Last30days".
QtySold_Last30days: =CALCULATE([TotQtySold],DATESBETWEEN(Calendar[Date],TODAY()-30,TODAY()))

Where, TotQtySold: =SUM(Sales[Qty_Sold])

The problem is that when I add the column "QtySold_Last30days" in the pivot table report all other items show up. Here is a picture of the pivot table with all items showing:

Is there a way to change the formula in the calculated field "QtySold_Last30days" and have it in a way that when it is dropped in the pivot table report only the items with negative quantity show up (like the picture below)?

Note: I can not link the "Inventory" and "Sales" tables because the columns contain duplicate values.

The excel file I am working with is uploaded here (394KB):
https://www.dropbox.com/s/wz41cviuovt3c42/Report_FSA.xlsx?dl=0

Thanks.

 

[Matt Allington]

create a new formula for qty sold last 30 days like this

=calculate([your current last 30 day measure goes here],Inventory)

use the above formula in your pivot instead of the current qty sold formula.

If if you want to know how it works, you can read my blog post here.
Many to Many Relationships in DAX Explained - Excelerator BI

 

[LBala]

Matt, perfect solution again!
Great information in the blog, thanks for sharing.