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

Pls Share the workbook. It really helps..

• ###### 16. Re: Fiscal Year to Date sales

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

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

by changing "=0"  to  "=1"

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

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