Very Useful YearToDate

Many people will have used YearToDate (or Year2Date) to calculate out whether the current date is Year to date for this year, last year, etc.:

YearToDate(date_value)

or

YearToDate(date_value, -1)

But there are some other values of YearToDate that make it very useful. The full syntax is:

YearToDate( date [ , yearoffset [ , firstmonth [ , todaydate] ] ] )

The third parameter is very useful because it allows us to use the one function for MonthToDate and QuarterToDate to compare Year-on-Year or dynamically change between YTD, MTD and QTD (there are functions InMonthToDate and InQuarterToDate that could be used but they would require different Year offset calculations and are less easy to make dynamic use of)

For example:

YearToDate(date_value, 0, Month(Today()))

gives us Month to date this year.

YearToDate(date_value, -1, Month(Today()))

gives us Month to date last year.

With a handy use of the Ceil function, we can also calculate Quarter to date:

Ceil(Month(Today())/3) * 3 - 2

This value will always return the first month of the quarter containing the Month number passed (in this case, Month(Today()) )

So, plugging this into our YearToDate:

YearToDate(date_value, 0, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date.

YearToDate(date_value, -1, Ceil(Month(Today())/3) * 3 - 2 )

gives us Quarter to date last year.

Use of appropriate variables means that we can give the user a very useful dynamic swap between YTD, QTD and MTD - if that is required.