Disclaimer: This is going to be one of those dates where the other person just constantly talks about work, because I will be going through some date formulas and how to create parameters to filter out different date views.

The formulas we will be using include various date functions. Tableau has a great native feature in the right pane of their calculated field builder which explains each formula quite clearly. You also need to create an excel file which lists 2 years worth of dates, just to give yourself a decent timeframe which looks like the squares below. Now, let’s move straight to the calculations.

For calculations which are structured as “Last Week/ Month/ Quarter/ Year”, their calculations are all structured in a similar manner.

When using datediff() tableau suggests having the more recent date appear first in the formula and it will give you a negative answer. As an example, datediff(‘month’, 2019-02-01, 2019-01-01) will give you -1. The calculations we will use are also all ‘if’ statements which follows this logic.

If the date difference in a specified periods equals -1, then it is considered as the last period.

Looking at the *Is Last Week *formula above, it is essentially finding if the date difference in ‘weeks’ between the date today, and the given date is -1. The date part between the quotation marks can be interchanged with month, quarter and year if that is the period you are after. These calculations default as a measure (green pill) but you will have to convert it to a dimension in order to use it as a filter.

We see that having just the last month is an easy calculation because you want to set the value to equal -1.

However, if you want the last few periods, you need to add in another condition, joined by AND.

If you want last 3 months, but you don’t have the extra condition, you will see this.

As you can see, all the future dates will also be included. However, with the 2^{nd} restriction, you will only keep it to last month. Following on with the calculation below, it basically looks for the period between 1 and 3 months. If it is, then give it a 1.

So far, we have made calculations for the last full weeks, or last full months, but if you are interested in calculations such as month to date or year to date, the 2^{nd} condition just needs to be testing if the date is less than today’s date, as seen in the *Current Calendar Year* calculation below. This will return everything from 1^{st} January or the year, up until yesterday’s date.

Now for something a little trickier – (Australian) Financial Year to Date

The financial year to date calculation requires a little more finesse than the other calculations. To follow the calculation for it below, you want to find if the year part of the date (after adding 6 months on to it) is the same year part of today’s date (after adding 6 months on to it). This means that on the 1^{st} July 2018, if you add 6 months on to the date, you will get 1^{st} January 2019, which will have the same year as any date up until 30^{th} June 2019.

Finally, for that added flair, you would want to chuck all these time periods into a parameter to let your end user control what they want to see. To do so, create a parameter with the controls as in the picture below.

Next you would want to put these parameters into a case statement (also shown below) and click and drag the calculation into colour in the marks card.