9 Replies Latest reply on Dec 9, 2015 12:12 PM by Rajeev Pandey

    Filter values up till current month

    Randy Allan

      I have data that spans the whole year, but I only want to show data from the beginning of the year up until the previous month.


      When poking around, I originally thought this would work but it will show data up to the current date.  [MY_DATE]<= TODAY()

      Changing TODAY() to MONTH() doesn't work.

        • 1. Re: Filter values up till current month
          Simon Runc

          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.

          • 2. Re: Filter values up till current month
            Randy Allan

            Hi thanks for the response. Yep, Tableau 9.0. Currently just using TODAY() as a workaround to get the latest date. I have data from January 2014 to December 2015. I want to apply a filter to my graph that only shows data from January 2014 to whatever the previous month of the current date. Today would be September 1st so I would want to return data up until August 2015.

            • 3. Re: Filter values up till current month
              Rody Zakovich

              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.




              1 of 1 people found this helpful
              • 4. Re: Filter values up till current month
                Rody Zakovich

                Sorry this can be made a lot simpler.


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




                2 of 2 people found this helpful
                • 5. Re: Filter values up till current month
                  Simon Runc

                  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]




                  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

                  1 of 1 people found this helpful
                  • 6. Re: Filter values up till current month
                    Cameron Sutcliff

                    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?



                    • 7. Re: Filter values up till current month
                      Saurabh Singhal

                      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.

                      • 8. Re: Filter values up till current month
                        Simon Runc

                        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




                        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

                        1 of 1 people found this helpful
                        • 9. Re: Filter values up till current month
                          Rajeev Pandey

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