3 Replies Latest reply on Oct 27, 2013 12:20 AM by . Indumon

    YTD start date on April... Help

    Nicholas Yu

      Hello all,

      I am currently writing a YTD sales total. But my company Year start at April 1st. The calculation below is below on calendar year. Can someone help me out here?

       

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

       

      Thank you so much!

        • 1. Re: YTD start date on April... Help
          Gengming Chen


          What you have answers sum of sales todate. TO set a differerent default starting month, you can use tableau to define the starting month of ficial year by right click on the date field -> default properties -> ficial year starts ..

          • 2. Re: YTD start date on April... Help
            Nicholas Yu

            That won't work. I need to use calculation field so I can return YTD on fields.

            • 3. Re: Re: YTD start date on April... Help
              . Indumon

              Hi Nicholas,

               

              I think the below calculation will suit for your requirement, I did a quick test and it is working for me.

              Please let us know if it is not working.

               

              Current Year:

              SUM(
              If Month(Today())>=4 Then
                  If (Year([Order Date])=Year(today()) And Month([Order Date])>=4 And [Order Date]< Today()) Then
                      [Sales]
                  End
              Else  
                  If ((Year([Order Date])=Year(today())-1 And Month([Order Date])>=4) 
                  or (Year([Order Date])=Year(today()) And Month([Order Date])<4) And [Order Date]< Today()) Then
                      [Sales]
                  End
              End
              )
              

               

              Last Year:

               

              SUM(
              If Month(Today())>=4 Then
                  If (Year([Order Date])=Year(today())-1 And Month([Order Date])>=4 And [Order Date]< Dateadd('year',-1,Today())) Then
                      [Sales]
                  End
              Else  
                  If ((Year([Order Date])=Year(today())-2 And Month([Order Date])>=4) 
                  or (Year([Order Date])=Year(today())-1 And Month([Order Date])<4) And [Order Date]< Dateadd('year',-1,Today())) Then
                      [Sales]
                  End
              End
              )