I'm struggling with how to accomplish something in a report. I have a Sales Analysis report that is showing the Current YTD month by month. I want to add a couple of columns - Prior Year YTD (which would be a total Amount), Prior Year Total Sales, and a % Change YOY. I understand I can do some of this with Calculate. For instance, I have added the following the "Prior YTD Total" calculated field:
Sum(if year([Invoice Date])=year(today()-1) then [Sales] end)
I can not; however, seem to add this to my report. I would like there to be a column to the right of my current years TOTAL which has the prior years totals.
I tried to add both years and just remove the months for the prior year but it removes the month detail for both the current and prior year. I'm suspecting this has been solved before but I'm not really sure what to search on in the Forum.
Any thoughts on how to put this together would be appreciated.