Pls Share the workbook. It really helps..
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)
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
MONTH([Adjusted Month-Year])<=MONTH([Adjusted Reference Date])
[Order Value (USD)]
Best Regards, Lenzy
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.
if year([Order Date]) = [YearParameter]
and month([Order Date]) <= [MonthParameter]
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 !