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 !
Could you share the workbook if possible?
It works does not works for Previous Fiscal YTD instead it shows data for Previous Fiscal year's month data (Ex: considering today's date as 13th Feb, 2020- the Previous fiscal YTD should show 1st April 2018 to 13th Feb 2019. But it is showing data from 1st April 2018 to 28th Feb 2019 )
Could you please help me out with getting data from 1st April 2018 to 13th Feb 2019?
Can you help me with Prior Year Fiscal YTD and Prior Year Fiscal MTD calculations?