cancel
Showing results for 
Search instead for 
Did you mean: 

Monthly Report and YearToDate function

Former Member
0 Kudos

I have a report that I run each month, sometime after the last day of the month.  It prompts the user to enter the ReportEndDate, which usually will be the last day of the month of the previous month.  I pull sales and COGS for that month.  The report also has YearToDate figures that is calculated using a formula that compares the invoice date to the function YearToDate.  I hadn't noticed an issue until I ran the report today, January 2, 2013.  Because it uses current date to determine the YearToDate, it was pulling nearly all zeros.  That makes sense.  So I found this note in the Online Help under YearToDate:

Note: If you would like to base the date range on a date other than today's date, you must either change the date via the Date/Time option in the Windows Control Panel, or change the report print date via the Set Print Date and Time command in the Report Menu.
I used the Set Print Date and Time command in the Report Menu and changed it to 12/31/2012,11:59:59 and the report worked great with accurate YTD numbers.
What I'd like to know is if there is an easy way to create a formula to set the print date automatically to the last day of the previous month so that the Year To Date figures are always accurate.  The monthly amounts are correct because I use the date that the use enters. It's the YTD that can be inaccurate because of date fluctuations.
I've searched and haven't found anything that answers this.  I would appreciate if anyone has already done this and could share with me.
Thank you very much. Happy New Year!
Jan

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member260594
Active Contributor
0 Kudos

Hello Jan,

I don't think you can use a formula to set the print date but you could use a formula to generate the year to date range that will return last year if the month is January or the current year from feb to Dec.

{datefield} in

date(year(maximum(lastfullmonth)), 1, 1) to date(year(maximum(lastfullmonth)), 12, 31)

Regards,

Graham