Year to Date (YTD) from Previous Year and Fiscal Year

Version 3

    Description:

     

    While there is a Year to Date option in a Relative Filter for a date, there is no Year to Date from the previous year. The following formulas use the current Day/Month/Year to determine whether a date falls into the prior year, ignoring February 29.

     

    Example Calculation:

     

    //YTD for previous year

    //replace [Date] with your desired date, returns True if the date is in the YTD from the previous year

    //presumes that there is no data that is future dated

     

    YEAR([Date]) == YEAR(TODAY())-1 AND (MONTH([Date]) < MONTH(TODAY()) OR (MONTH([Date]) == MONTH(TODAY()) AND DAY([Date]) <= DAY(TODAY())))

     

    Also, here's an alternative calc that can be used for the YTD for the prior year and current year

     

    //YTD for previous year and current year

    //replace [Date] with your desired date, returns True if the date is in the YTD previous or current

    //presumes that there is no data that is future dated

     

    YEAR([Date]) >= YEAR(TODAY())-1 AND (MONTH([Date]) < MONTH(TODAY()) OR (MONTH([Date]) == MONTH(TODAY()) AND DAY([Date]) <= DAY(TODAY())))

     

    In the case that the fiscal year does not match the calendar year, we first need to be able to identify the fiscal year of any given year. Here's a basic calc:

     

    //Fiscal year starting 10/1 (US Federal)

    //Change the 3 to however many months are in the year for forward fiscal years,

    // e.g. 6 for FY2014 beginning 2013-07-01

    DATEPART('year',DATEADD('month',3,[Date]))

     

    Now for calc that can will return True if the date is in the current fiscal year:

     

    //Replace [Date] with your desired date

    //The DATEADD of 3 months is for a forward fiscal year starting 1 October, like the US Federal fiscal year

    [Date] >= DATE(STR(DATEPART('year',DATEADD('month',3,TODAY()))-1) + "-10-01")

    AND [Date] <= TODAY()

     

    And a calc that returns true if the date falls into the prior fiscal year YTD:

     

    [Date] >= DATE(STR(DATEPART('year',DATEADD('month',3,TODAY()))-2) + "-10-01")

      AND [Date] <= DATEADD('year',-1,TODAY())

     

    And finally, a calc that returns true if the date falls into the current or prior fiscal year YTD:

     

    ([Date] >= DATE(STR(DATEPART('year',DATEADD('month',3,TODAY()))-1) + "-10-01")

      AND [Date] <= TODAY())

    OR

    ([Date] >= DATE(STR(DATEPART('year',DATEADD('month',3,TODAY() ))-2) + "-10-01")

      AND [Date] <= DATEADD('year',-1,TODAY()))

     

    The attached workbook demonstrates the fiscal year calcs.