3 Replies Latest reply on May 7, 2018 4:31 AM by Okechukwu Ossai

    MTD

    chowdary v

      Hi All,

       

      I have sales data may 1st to may 31st but i want TO calculate MTD that means may 1st to may 7th how can I calculate I have to use below formula

       

      MTD:

       

      sum(IF [ActualDate] <= TODAY() and

      DATEDIFF('month',[ActualDate],TODAY())=0 THEN [Sales] else 1 END)

       

      is it correct or not.

       

      Thanks,

      Veeru

        • 1. Re: MTD
          suman kumar

          Hi chowdary v

           

          Yes, This calculation is correct. And if you want to use YTD then just use Year instead of Month.

           

          Thanks

          Suman

          • 2. Re: MTD
            sudheer.kumar.5

            Hello Chowdary,

             

            The calculation is correct only.

             

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

             

            What exactly the issue you are facing?

             

            My observation: I've data in excel like below:

             

            DateSales
            15-Apr1
            16-Apr2
            17-Apr3
            18-Apr4
            19-Apr5
            20-Apr6
            21-Apr7
            22-Apr8
            23-Apr9
            24-Apr10

            When double click on the Date cell in excel, it will show the year as 2018. When I copy the same to the clipboard and paste in Tableau, Tableau considered year as 1900.

             

             

            Hope you are also facing the same?

             

            S

            • 3. Re: MTD
              Okechukwu Ossai

              Hi Chowdary,

               

              Remove the ' Else 1' clause in your formula or you can use the formula below.

               

              Create calculated field [MTD Sales]

              IF DATETRUNC('month', [ActualDate]) = DATETRUNC('month', TODAY()) AND [ActualDate] <= TODAY() THEN [Sales] END

               

              Hope this helps.

              Ossai