It should not be summed. with the InYearTodate function you can tell it when to stop. In this case it is by using the Today() function, which says "Is the date in the current fiscal year, up to today?" if so then return true. If you wanted to stop at the end of the fiscal year, you could put in the last day of the current fiscal year. This would only return sales for the current fiscal year, and not the next year. I threw an example date in the data shown previously and the sum of those sales did not get added into my total.
your table have a column fiscal year, so I guess you see there only years like 2012, 2013 or 2015. If yes then try to use following
sum({$<([Fiscal year]='2015')}>}Sales) if you put the field "fiscal year" in a list box you should use the following formula sum({$<[Fiscal year]='*')}>}Sales), depending which year you choose you will get the sum of this year.
Thank you for your answer.. the request that i have is presenting the current fiscal sales without clicking on the list box or hard code the FY year name within set analysis
Thank you Charles but it doesnt work.. how can we pass the end of the fiscal year here..as for today's date the last date of the current fiscal year 3/31/2015 and starts on 4/1/2014 and the number of the year is 2015..
We shouldn't hard code the date so how can i pass the last date dynamically?
instead of using the today() function to pass today as the 'last day in the YTD fiscal year' use the function YearEnd(). Thus you would be finding the End of the current year if you use YearEnd(Today(), 0, 4 )
This says the YearEnd = 3/31/15
So substitute the today() in the line above with: YearEnd(Today(), 0, 4 )
THus if the date is within fiscal year 2015 it will grab the sales amount and sum them up .Thus it is not hard coding, and when you then enter Fiscal Year 2016, it will automatically grab 2016 data. If you want to show 2015s data again somewhere just use:
