Date Logic in Tableau

+1 vote

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?

Jul 25, 2018 in Tableau by Atul
• 10,240 points
40 views

1 answer to this question.

0 votes

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.

answered Jul 26, 2018 by Atul
• 5,530 points

Related Questions In Tableau

0 votes
0 answers

How can I sort date field in presence of another date field in Tableau?

Goal: Sort the new ship date in ...READ MORE

Apr 10, 2018 in Tableau by ghost
• 1,790 points
123 views
0 votes
1 answer

How to convert string to date format in Tableau?

You can create the following calculated field: Dateparse('EEE ...READ MORE

answered Jun 29, 2018 in Tableau by Atul
• 5,530 points
1,188 views
0 votes
1 answer

How to merge Year ,Month and date in Order Date in Tableau?

Hi, you can simply drag the Order ...READ MORE

answered Aug 24, 2018 in Tableau by AwesomeSauce
• 860 points
75 views
0 votes
1 answer

Sorting Attribute Date in Tableau

Hi,  You can sort date field in ascending/descending ...READ MORE

answered Mar 12 in Tableau by Cherukuri
• 25,580 points
192 views
0 votes
1 answer

Calculate difference of 2 dates

Hi priyanka, Use the datediff function to calculate ...READ MORE

answered Jun 4 in Tableau by Sindhu
24 views
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
1,773 views
+1 vote
1 answer
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
76 views
0 votes
1 answer

Is it possible to merge year, month and date in Tableau?

Remove all the date pills and drag ...READ MORE

answered May 9, 2018 in Tableau by Atul
• 5,530 points
35 views
0 votes
1 answer

Error: Comaparing date value with YEAR in Tableau

You have to ensure that the comparisons ...READ MORE

answered Jul 11, 2018 in Tableau by Atul
• 5,530 points
42 views