2 Replies Latest reply: Jun 28, 2016 3:25 PM by Troy Strachan RSS

    InMonths function giving strange results

    Troy Strachan

      Hey everyone,

      I have a question regarding the inmonths function.


      I'm trying to colour code sales from customers that are older that 6 months ago.

      Here is what I'm using.



      if (inmonths (6, Max( [Order Date] ), Today(), -1), Red() )


      Problem: January, February, March, April, May, and June of previous years are not being colour coded. I have attached a small screen shot Due to the number of records you can't see much of the previous information.


      My source for inmonths function:


        • Re: InMonths function giving strange results
          Sunny Talwar

          You want to color 6 months for this year and last year? May be this:


          If(InMonths(6,Max([Order Date]), Today(), -1) or InMonths(6,Max([Order Date]), AddYears(Today(), -1), -1), Red())

            • Re: InMonths function giving strange results
              Troy Strachan

              This is close, but I want to set a user defined variable for how many months they want to go back.


              Plus I want to highlight everything from 6 months back.

              So if customer A hasn't purchased anything since December 2014 I want to be able to see that highlighted.


              With the second part of your equation, it's limiting the scope to 1 year prior.


              One solution I came up with was:

              if( (Year (Max ( [Order Date] ) ) <> Year (Today () ) or ( inmonths ( varMonthLimit, Max ( [Order Date] ), Today (), -1) ) ) , Red() )


              Which highlights everything NOT within this calendar year and X months back. Problem with this, is that in January, everything in December is highlighted red because it's not in the calendar year.