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]


          • 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


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


            [Order Value (USD)]



            Best Regards, Lenzy

            • 18. Re: Fiscal Year to Date sales

              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



              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