How to select the last day of a dynamic period in Tableau || Let’s have (a word about) date(s) (6/6)

For today, we will do a rather short quickie.

Imagine you are tasked with providing a dynamic view that can alter between say weekly, monthly, yearly and you want to show always time-period end values.

Create a string parameter and insert there whatever you need, for example the values outlined before.

Then, create this calculation:

//End of period
DATEADD( [parameter], 1, DATETRUNC( [parameter], [date field]) ) - 1

What this will do is, reading from the inside to the outside, it will first truncate your date field to the selected timeframe. Truncating always puts it back to the first of that period, i.e. truncating April 15th 2022 to month will yield April 1st 2022.

From here, we simply add an instance of our selected time frame. Sticking with the afore example, adding one month to April 1st yields May 1st.

And from that, we simply subtract 1 (day) and get April 30th.

So, you can now play with this, either use it as a filter for example like

[Date] = [End of period]

This will give you a boolean you can play with to filter or directly use in other calculations.

You can also amend it to always filter to the last of a period unless the period isn’t fully done, then filter to today like this

[Date] = [End of period]
OR
[Date] = TODAY()

Nothing spectacular today but it might come in quite handy.

And that’s my quickie for today.

I hope you found that useful. As always appreciate your feedback.

Until next time.

Steffen