Return Monthly Sales for Prior Year on a as of date report

[KatieDLR]

I have a report that runs Sales $ automatically every Monday morning for As of the current date of the month. I want to add another column to return the same information for that month of the prior year. I added the following expression but it keeps returning $0 and not the actual sales $. It works fine if I use it for another report that is run on demand and not with the as of function.

IF([Sales Analysis].[Sales Dates].[Year]=?PrmYear?-1 and [Sales Analysis].[Sales Dates].[Month]=?PrmMonth?)
Then([Sales Analysis].[Sales Metrics Combined].[Combined Net Sales)
Else(0)

[MFGF]

I have a report that runs Sales $ automatically every Monday morning for As of the current date of the month. I want to add another column to return the same information for that month of the prior year. I added the following expression but it keeps returning $0 and not the actual sales $. It works fine if I use it for another report that is run on demand and not with the as of function.

IF([Sales Analysis].[Sales Dates].[Year]=?PrmYear?-1 and [Sales Analysis].[Sales Dates].[Month]=?PrmMonth?)
Then([Sales Analysis].[Sales Metrics Combined].[Combined Net Sales)
Else(0)

Hi,

What filters do you currently have defined in your report? I'm guessing your report currently returns rows only for the selected year and month? You probably need to change the filter to select rows for both the selected year and month and the previous year and month. You'd then need a similar IF statement to collect the current year/month values in your report as well as the prior year/month shown above.

Cheers!

MF.

[KatieDLR]

Thanks for your suggestion! This is the current date filter I have so it will pull the as of date of the current year.
I have been trying to modify to get it to pull the as of date of the current or prior year but can't seem to get it to work.
([Sales Analysis].[Sales Dates].[Invoice Date ISO]>= add_months(_first_of_month(current_date),-0))

[MFGF]

Thanks for your suggestion! This is the current date filter I have so it will pull the as of date of the current year.
I have been trying to modify to get it to pull the as of date of the current or prior year but can't seem to get it to work.
([Sales Analysis].[Sales Dates].[Invoice Date ISO]>= add_months(_first_of_month(current_date),-0))

Hi,

Where do your ?PrmYear? and your ?PrmMonth? parameters get used? I assumed you had filters that referenced them?

I'm a little confused by your filter expression above - it looks like you are adding -0 months to the date that is the first day of the current month. Is it a typo?

MF.