1 2 Previous Next 18 Replies Latest reply on Mar 24, 2018 4:07 PM by pavani.p.1

    Fiscal Year to Date sales

    Drew Lemon

      hello All,

       

      I've been using the following calculated field to sum my sales as of year to date. The problem is, I need to adjust to be fiscal year to date. Any suggestions as how this could be done?

       

      IF DATEDIFF('year',[Month-Year],[Reference Date])=0

      AND

      MONTH([Month-Year])<=MONTH([Reference Date])

      THEN

      [Order Value (USD)]

      END

        • 1. Re: Fiscal Year to Date sales
          Steve Mayer

          Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

           

          1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

           

          DATEADD('month', 9, [Month-Year])

           

          2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

           

          DATEADD('month', 9, [Reference Date])

           

          3) Update your measure to use the adjusted dates instead of your current dates:

           

          IF DATEDIFF('year',[Adjusted Month-Year], [Adjusted Reference Date])=0

          AND

          MONTH([Adjusted Month-Year])<=MONTH([Adjusted Reference Date])

          THEN

          [Order Value (USD)]

          END

           

          If your fiscal year starts in July, then you'll to need change the first two calculations to offset by 6 months instead of 9. If it starts in October, then offset by 3 months.

           

          Hope this helps,

           

          -Steve

          2 of 2 people found this helpful
          • 2. Re: Fiscal Year to Date sales
            Drew Lemon

            Thank you- worked like a charm!

            • 3. Re: Fiscal Year to Date sales
              satish.parvathaneni

              Hi Steve,

               

              Our current FiscalYear Start's from July .

               

              I am looking to Calculate YTD , QTD  Sales can i use the above ??

               

              Regards,

              Satish.p

              • 4. Re: Fiscal Year to Date sales
                Shankar Mishra

                DATEADD('month', 9, [Month-Year])

                 

                 

                [Month-Year]what does it signify??

                • 5. Re: Fiscal Year to Date sales
                  Shankar Mishra

                  DATEADD('month', 9, [Month-Year]) -------------[Month-Year]??????

                   

                   

                   

                  DATEADD('month', 9, [Reference Date])   [Reference Date]-----???

                  • 6. Re: Fiscal Year to Date sales
                    Tableau kumar

                    Hi,

                     

                    We can define the First month of Fiscal Year.

                    Creating Indian Fiscal Calendar

                     

                    Best Regards

                    Kumar

                    • 7. Re: Fiscal Year to Date sales
                      Shankar Mishra

                      i am not getting your point .

                       

                      please provide me the  calculation for ytd Calculation according to fiscal year

                      • 8. Re: Fiscal Year to Date sales
                        Steve Mayer

                        Satish -

                        You should be able to use the above technique but offset by 6 months instead of 9 months because your fiscal year starts in July. You should study the workbook from the correct answer submission to understand how the offset is working, because it is pretty tricky.

                         

                        -Steve

                        • 9. Re: Fiscal Year to Date sales
                          Shankar Mishra

                          Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

                           

                          1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

                           

                          DATEADD('month', 9, [Month-Year])

                           

                          2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

                           

                          DATEADD('month', 9, [Reference Date])

                           

                          3) Update your measure to use the adjusted dates instead of your current dates:

                           

                          IF DATEDIFF('year',[Adjusted Month-Year], [Adjusted Reference Date])=0

                          AND

                          MONTH([Adjusted Month-Year])<=MONTH([Adjusted Reference Date])

                          THEN

                          [Order Value (USD)]

                          END

                           

                          Problem :   [Month-Year] in above calcluation i am not getting this filed it will be my posting date or some other calculated field.

                          [Reference Date] not getting

                          • 10. Re: Fiscal Year to Date sales
                            Steve Mayer

                            Shankar - you might ask Drew to attach his workbook so that you can see how he did it.

                             

                            [Month-Year] is the name of his date dimension in his data source. You'll have to substitute the name of your date dimension for [Month-Year].

                             

                            [Reference Date] is the name of his date parameter in his data source. You will also have to create a date parameter in your data source, and update the calculated field with the name of your date parameter.

                             

                            Hope this helps,

                             

                            -Steve

                            • 11. Re: Fiscal Year to Date sales
                              Shankar Mishra

                              Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

                               

                              1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

                               

                              DATEADD('month', 9, [Month-Year])

                               

                              2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

                               

                              DATEADD('month', 9, [Reference Date])

                               

                              3) Update your measure to use the adjusted dates instead of your current dates:

                               

                              IF DATEDIFF('year',[Adjusted Month-Year], [Adjusted Reference Date])=0

                              AND

                              MONTH([Adjusted Month-Year])<=MONTH([Adjusted Reference Date])

                              THEN

                              [Order Value (USD)]

                              END

                               

                              Problem :   [Month-Year] in above calcluation i am not getting this filed it will be my posting date or some other calculated field.

                              [Reference Date] not getting

                              • 12. Re: Fiscal Year to Date sales
                                Shankar Mishra

                                1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

                                 

                                DATEADD('month', 9, [Month-Year])-----------------????from where we will get [Month-Year]

                                 

                                2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

                                 

                                DATEADD('month', 9, [Reference Date])----------------????from where we will get [Reference Date]

                                 

                                3) Update your measure to use the adjusted dates instead of your current dates:

                                 

                                IF DATEDIFF('year',[Adjusted Month-Year], [Adjusted Reference Date])=0

                                AND

                                MONTH([Adjusted Month-Year])<=MONTH([Adjusted Reference Date])

                                THEN

                                [Order Value (USD)]

                                END

                                • 13. Re: Fiscal Year to Date sales
                                  Shankar Mishra

                                  DATEADD('month', 9, [Month-Year])

                                  Please Make me understand from where i have to calculate this filed  [Month-Year].

                                  • 14. Re: Fiscal Year to Date sales
                                    Steve Gesuale

                                    Helpful post - thanks!

                                    1 2 Previous Next