Tableau Tips : Filtering Between Dates

Hi All,

Today I am going to talk about a scenario in which I had an encounter with dates in Tableau.

The objective was to get values of a measure for the last month corresponding to the max date. Taking example of the sample superstore Data set here is what we would like to achieve. The Sample superstore data has the sales value till 31^stDecember, so our objective is to get the last month sales corresponding to the max sales date (Nov 2016).

We start by Creating date field called Date End. This would identify the staring of the last month in our data. The result Date Would be 1-12-2016

Similarly we create a date filed called Date Start , that identifies the first date of the month before the last month. The Result Date would be 1-11-2016

Now  the next step would be to truncate this dates to the day level and then fix that across the view so that these can be compared to the other dates in the view.

Create a calculated field Date End 2

Create a calculated field Date Start 2

 

 

 

The final step now left is to create a filter based on the dates so that we can get the desired result :

Create a filter :

Now place this filter in the filter table and select : Include.

Bring Sales into Rows and dates into columns . Convert from Year(Order Date) to Day(Order Date) .

The final output is what we wanted to achieve.

 

Hope you guys liked it.

Comments are appreciated.

PS : This can be just done in one step by creating a filter in the following fashion :
NOT(DATETRUNC(‘month’,[Order Date]) <>
DATEADD(‘month’,-1,
DATETRUNC(‘month’,{FIXED: MAX([Order Date])})))

I learned it the hard way. Helps to remember how stupid I was when I spent 30 mins thinking about the long way.