1 2 Previous Next 18 Replies Latest reply on Mar 24, 2018 4:07 PM by pavani.p.1 Go to original post
      • 15. Re: Fiscal Year to Date sales
        Venkatesh Pilla

        Pls Share the workbook. It really helps..

        • 16. Re: Fiscal Year to Date sales
          John Gough

          Here's a solution you might try. It's a bit clumsy around leap years though:

           

          // Fiscal Year To Date Calculation

          // Assumes September, 1st as start of FY.

           

           

          // Makes rough accomodation for leap year in 'or' statement.

          // Anytime the last day of the fiscal year is day 366 historically -

          // and the current day of the fiscal year is 365, it should bring in the

          // production totals for both days 365 and 366 together.

           

           

          IF (datepart('dayofyear', (DATEADD('day',122,[Transaction Date]))) <= datepart('dayofyear', (DATEADD('day',122,today()))))

              or ((datepart('dayofyear', (DATEADD('day',122,[Transaction Date]))))= 366 and (datepart('dayofyear', (DATEADD('day',122,today())))) = 365)

          THEN [Amount]

          END

          • 17. Re: Fiscal Year to Date sales
            lenzy petty

            Thanks Steve!  for the Best solution I've seen for Fiscal Year YTD!  Also works for Previous Fiscal YTD

            by changing "=0"  to  "=1"

            Shinichiro Murakami

             

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

            AND

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

            THEN

            [Order Value (USD)]

            END

             

            Best Regards, Lenzy

            • 18. Re: Fiscal Year to Date sales
              pavani.p.1

              Hi  STEVE MAYER

               

              Your reply is very helpful but I have a Question here. what if am using dynamic parameters(Year Parameter, Month Parameter) to calculate YTD . I am giving user the control to select year and month of his interest. How am I going to calculate Modified YTD for my Fiscal year?

               

              I am using following YTD calculation in filter and selecting it to 1 to calculate YTD for all my Metrics.

               

              YTD =

              if year([Order Date]) = [YearParameter]

              and month([Order Date]) <= [MonthParameter]

              then 1

              end

               

              Lets Assume my Fiscal year starts in April.

               

              Adjusted OrderDate :

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

               

              How Am I going to adjust my Year and Month Parameters to calculate Modified YTD?

               

              Thanks in Advance !

              1 2 Previous Next