Date Logic - Tableau

1
votes

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?

1
logictableau-api
Just a few Tableau features that I want to make sure you are aware of. First, you know that you can set the fiscal year start for date fields right? Right cilck on a date field in the data pane and set its date properties. You can do that for data sources too. This helps with sorting and display -- doesn't fix everything. – Alex Blakemore
The other feature that you could start to learn about is table calculations. There are several moving parts to them. Read the help, blogs and perhaps Joshua Milligan's Tableau 10 Bootcamp book. If you have multiple months in a viz, you can often define table calcs that have logic like you want that adapt to new data without having to be edited each year. Read about partitioning and addressing and experiment to make sure you understand how that works, otherwise table calcs will seem mysterious. – Alex Blakemore
sure Alex, I take your suggestion. Thanks – feb

1 Answers

1
votes

Try this:

Create 2 calcualted fields for start date and end date, Where 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 calcualted field which will create the filter and add the formula to filter to get the require data.

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

Add to filter and then select True

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