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

SDT — Thu, 21 Jan 2021 16:59:13 GMT

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.

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

GaryGiles — Thu, 21 Jan 2021 18:37:38 GMT

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.

topic Re: Sum Prior year Measure only for products sold in the current year? in App Development

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

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