hi Randy,

Yes we can certainly help with this, but a couple of Qs!

Firstly can you confirm you are using Tableau 9.0? and also are you using Today() as a work around to get the latest date? (i.e. would you rather drive this from the last date in the data...just thinking of when data loads fail...etc.)

Do you also need to pick up the current year? or do you already have this set in a filter (or irrelevant as you only ever have current year in your data)

If you let me know on the above, I can show you how to do this.

Hello Randy,

Have your tried something like this?

[You Date] <=   DATEADD('day', -1, DATETRUNC('month', TODAY()))

the second part of this Calc will always return the Last Day of the Previous Month.

So this calc would limit your dates to only return data up until the Last Day of the Previous Month.

Regards,

Rody

Sorry this can be made a lot simpler.

[You Date] < DATETRUNC('month', TODAY())

Regards,

Rody

hi Randy,

My solution is a variation on Rody's, however I'm using LoDs so we filter out to the last completed month. This might not be your exact final solution, but hopefully you can adapt the logic to your exact case.

btw in the attached example I've filtered the superstore to 2014, to mimic your case, but you could equally use this method to filter to Max year too.

So first I use a LoD calculation to put against every row, the Last Month in the data. This is called 'Max Month' and is

{MAX(DATEPART('month',[Order Date]))}

The datepart bit returns the integer of the last month in the data (12 in this case, as the last date is 31/12/2014)

I can then, like Rody's, use this to filter out the months I don't want. In my example the field is called 'Filter on Last Full Month' and is

DATEPART('month',[Order Date]) < [Max Month]

(the

Hopefully you can see what's going on in the 'How it works' tab, where I've detailed out how the calc, works, and then in the next tab I've show it in action!

If this doesn't make sense, or doesn't solve your issue please post back

Hey Randy!

I have a similar question filtering out the last month, though my structure is a little different...

We have a filter to show the last X Months/Quarters/Years of our records, but I'd like to just exclude the penultimate month in our calculations as a data refresh on 12/9/15 doesn't include all of Dec, 2015's data.

Any suggestions?

Hi Cameron,

If I understand correctly, in your calculations you do not want to include Dec, 15 as part of last 13 months. If that is so, then using Relative Date filter may not help as it will be anchored to current system time/date by default. For such scenario, LoD expressions should help where you can define the latest date as Max(DB Date) - the way Simon has explained in previous post.

Hi Cameron,

If you want to use the Relative Date filter, and not have it anchored to today you might find this useful

Base Relative Date Filter on Latest Date in Data

You'll need to use with care (read the comments from Pooja and Rody) as you can't control the DATETRUNC part of the relative date filter (eg. if you viz is at Week Level, and your user uses last X months).

Personally, I'd create a Month Index

DATEDIFF('month',[date],{MAX([Date])})*-1

This means all you months are dynamically indexed from 0 backwards (so 0, -1, -2, -3...) with 0 being the current month. You can then set up a parameter letting the user select how many months back they can see (filtering out the Monthindex = 0) and adjusting your filter formula to account for this. The only draw back is on the last day of the month, there is potentially all that months data to view, but as this would be MonthIndex = 0, people wouldn't see it until you got data in for the 1st of the following month. So 1 day out of 30 its not perfect (and allowing that this will sometimes fall on a weekend...etc.) its a cost I'm happy with.

You could even, using the clever parameter naming convention, I've commented on here, https://community.tableau.com/docs/DOC-6205#comment-13722  create a second parameter so the user could select week, month...etc. and use this in your Periodindex...so DATEDIFF([parameter.period_selector],[date],{MAX([Date])})*-1. This way your user would have the same functionality (basically re-creating the relative date filter with 2 parameters), but you control how its handled (Tableau calculation wise).

....just a thought

Simon Runc : This is supercool.I will Definitely use this technique