3 Replies Latest reply on May 19, 2013 9:01 PM by Lester Anciro

    Getting Month-to-Date sales using calculated field.

    Lester Anciro

      Hi guys,

       

      I just want to ask if there is a way I can get my Month-to-Date sales using a calculated field?

       

      I tried  this computation but it keeps on giving me ONE DAY sales.

       

      IF [Order Date] AND DATEDIFF('month',[Order Date],Today())= 0 THEN [Sales] END

      asd.bmp

       

      The 2.54M and 2.82M in above data must be more than 60M if it is month-to-date.

       

      Thanks in advance!

       

       


        • 1. Re: Getting Month-to-Date sales using calculated field.
          Jim Wahl

          Hi Lester,

           

          DATEDIFF('month', start, end) is giving you the number of months between these dates. Your graph is a bit confusing. If the date on the x-axis is Order Date, I'm not sure why you'd have any values in the MTD calculation. All Order Dates should have a DATEDIFF > 0, given it's May.

           

          But if you want to match the months, you could use MONTH([Order Date]) == MONTH(TODAY()). And for the month to date, you could do the same with DAY([Order Date]) <= DAY(TODAY()).

           

          Your formula might be:

               IF DAY([Order Date]) <= DAY(TODAY()) AND MONTH([Order Date]) == MONTH(TODAY()) THEN [Sales] END

           

          But if you're already segmenting the view by month, as you have in your bar chart, you might just use

               IF DAY([Order Date]) <= DAY(TODAY()) THEN [Sales] END

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Getting Month-to-Date sales using calculated field.
            Lester Anciro

            Hi Jim.

             

            Thanks for the helpful answer..

            And sorry for the graph.

             

            I'll explain it more clearly,

             

            I have the data for month of March only, so I created a sample date with DATE = "3/26/13"

             

            I want to get the Sales from March 1 up to the sample date March 26, how do I do it?

             

             

            Thanks again.

             

            Lester

            • 3. Re: Getting Month-to-Date sales using calculated field.
              Lester Anciro

              Got my answer to my question

               

              Thanks for the help!