+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 26, 2018 in Tableau 821 views

1 answer to this question.

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 27, 2018 by
• 5,550 points

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

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

How to convert string to date format in Tableau?

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

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

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

Sorting Attribute Date in Tableau

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

Calculate difference of 2 dates

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

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

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

+1 vote

How to Link Dashboard with URL to enable it to open in the same explorer page

Add this to the end of the ...READ MORE

Calculated filed with if-then

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