Sum Prior year Measure only for products sold in the current year?

[SDT]

Hi,

Using the following fields in the model: FiscalYear, InvoiceAmount, and ProductID I need to write a sum with set analysis that will sum all the InvoiceAmt from the prior fiscal year but only for products sold in the current fiscal year.

I thought this might do it:

SUM({$<FiscalYear={"FY20"}>*$<ProductID=P({FiscalYear={"FY21"}, InvoiceAmount={">0"} ProductID)>} InvoiceAmount)

It is not producing the expected results since there are rows in the table for a productID with zero InvoiceAmount in the current year and >0 in the prior year.

[GaryGiles · Accepted solution]

I don't think you need to use an intersection in your set analysis.  Also, the P() function to be bracketed with {< >}.  Try this:

SUM({$<FiscalYear={"FY20"},ProductID=P({<FiscalYear={"FY21"}, InvoiceAmount={">0"}>} ProductID)>} InvoiceAmount)

Also, not sure that you need InvoiceAmount={">0"}, but you can test that.