2 Replies Latest reply on May 8, 2018 1:48 PM by Jim Dehner

    Month till yesterday

    David Núñez
    David Núñez May 8, 2018 12:02 PM

      Hi. I have a calculated field which helps me display my MTD data. Is it possible to change it to see month till yesterday?

       

      IF DATEDIFF('year',[date],Today())= 0

      AND

      datename('month',[date]) == datename('month',today())

      and

      datepart('day',[date])<=datepart('day',today())

      THEN [sales] END

       

      Thanks in advance

        • 1. Re: Month till yesterday
          Deepak Rai
          Deepak Rai Ambassador May 8, 2018 12:12 PM (in response to David Núñez)

          If Year(Order Date)=Year(Today())

          AND

          MONTH (Order date)=Month(Today)

          AND

          Day(Order date)<=Day(Today())-1

          THEN

          Sales

          END

           

          This should also work

          1 of 1 people found this helpful
          • 2. Re: Month till yesterday
            Jim Dehner
            Jim Dehner Ambassador May 8, 2018 1:48 PM (in response to David Núñez)

            Hi David - I'm a fan of using datetrunc()   -t returns an actual date - so to get MTD through yesterday is

                      if datetrunc('month',[Order Date]) = datetrunc('month',today()-1)   and datetrunc('day',[Order Date])<=datetrunc('day',today()-1)  then [Sales] end

             

            Datetrunc('month', today()) returns the first day of the month May 1,2018 likewise datetrunc('day',today()-1)   returns May 7,2018

             

            I just like working working with actual dates whe setting ranges

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful