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

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!!!

• ###### 1. Re: Trailing 12 months formula to include DAY based on parameter date

Per the instructions for posts one should always include a packaged workbook. Please add it to your post.

From the forum, Getting Started in the Forums :

It really does help the helpers help to answer the question more quickly, typically with less follow-up questions

• ###### 2. Re: Trailing 12 months formula to include DAY based on parameter date

Can you try something like this for the trailing 12 months a year before :

IF datediff("month", [Enter Date], [Business Date])=-12  then (if  day([Business Date])<={day(max([Business Date]))}                                                                                                              then [ROOM_REVENUE] END)

ELSEIF datediff("month", [Enter Date], [Business Date])<-12 AND datediff("month", [Enter Date], [Business Date])>-24

Then ([ROOM_REVENUE]) END.

Basically if it is the last month of prior year range then take value till the day of latest current month.

• ###### 3. Re: Trailing 12 months formula to include DAY based on parameter date

Ankit, thank you thank you!!! That is a brilliant solution!! Very much appreciated!!