4 Replies Latest reply on Jul 20, 2018 8:57 AM by Ankit Bansal

    Trailing 12 months formula to include DAY based on parameter date

    Pete Haenggi

      Hello All,

       

      I'm trying to capture the trailing 12 months for this year and the trailing 12 months of the same time the year before.  I can easily calculate trailing 12 months for revenue this year using the formula: IF datediff("month", [Enter Date], [Business Date])>-12 Then ([ROOM_REVENUE]) END.  I can also calculate the trailing 12 months the year prior by using this formula:  IF datediff("month", [Enter Date], [Business Date])<-11 AND datediff("month", [Enter Date], [Business Date])>-24 Then ([ROOM_REVENUE]) END

       

      However, I use a parameter named [Enter Date] used in the preceding formulas, which gives the user the ability to select any date.   The issue I'm having is that the person can enter a date of say 07/20/18.  This will work fine for the current trailing 12 months, but not for the trailing 12 months the year before, as the formula's are not taking into account DAY.  For the trailing 12 months the year before it would include 11 additional days for July 2017 (07/21/17 to 07/31/17) thereby throwing off the grand totals.  I can't seem to incorporate DAY into the formula and have it work correctly.  Can anyone help here? I can upload a workbook if necessary as well.

       

       

      Thanks!!!