Date Logic in Tableau

Question

Could someone help me achieve the date logic as below:

I work for a fiscal year starting from Oct to Sep, once I finish the fiscal year and step into a new one I want the last month of last fiscal year's sales for reference until the end of new fiscal year. For example, now in Sep'17 the fiscal year ended but I want Sep'17 sales number to be shown in graph as reference until next Sep'18 later that I want Sep'18 number to be shown until Sep'19 and so on so forth.

The logic I have arrived is not a permanent solution as it requires editing once I step into Year 2018, it is as below:

IF YEAR([Invoice Date]) = YEAR(TODAY()) AND 
DATEPART('month', [Invoice Date]) = 9 THEN [Sales] END

once I step into Year 2018, I need to make change to the above logic like:

IF YEAR([Invoice Date]) = YEAR(TODAY())**-1** AND 
DATEPART('month', [Invoice Date]) = 9 THEN [Sales] END

Is there a way to achieve permanent solution without editing the logic?

Answer 1

Try this:

Create 2 calculated fields for start date and end date, Where the start date is September last year and end date is September current year.

Start Date:

DATEADD('month',-1,MAKEDATE(YEAR(TODAY())-1,MONTH(TODAY()),01 ) )

End Date:

DATEADD('month',-1,MAKEDATE(YEAR(TODAY()),MONTH(TODAY()),01 ) )

Now one more calculated field which will create the filter and add the formula to filter to get the required data.

[Order Date]>=[Start Date] AND 
[Order Date] <=[End Date]

Add to filter and then select True

Note: Here today function means the start of the fiscal year that you need to manage.