Calculating Year To Date dynamically

The YearToDate function is one of those that you might use quite often. Mostly, it will be used with default parameters, or perhaps with the offset specified. But there are a number of parameters to this function that can make it even more useful.

In this recipe, we are going to examine using the function to deal with a user entering the year end date dynamically. We will also base it off a financial year starting in April.

Getting ready

Load the following script:

Load
  Date(TempDate) As Date,
  Year(YearStart(TempDate, 0, 4)) As FY,
  Dual(
    Text(Month(TempDate)), 
    Num(Month(MonthStart(TempDate, -3)))
    ) As Period,
  Dual(
    Year(YearStart(TempDate, 0, 4)) & '-' 
      & Num(Month(MonthStart(TempDate, -3)), '00'),
    MonthStart(TempDate)
    ) As FYPeriod,
  Year(TempDate) + Month(TempDate) + Day(TempDate) As Sales
  ;
Load
  RecNo()-1+Floor(MakeDate(2012)) As TempDate
AutoGenerate(730);

How to do it…

Follow these steps find out how to...