top of page

Dynamic Month Range Filters

Updated: Aug 25, 2023

Do you want to automatically filter your dashboard by the last 3 months or 6 months?


In my public dashboard here, and in my previous blog post here, I showed you how to create rolling sum calculations for 90 days, 120 days and 1 year.


But what if you just want to filter your dashboard to automatically look at the last 3 months, 6 months, etc.? You can achieve this with calculated fields.


First I create the calculated fields to pull the Latest Month;


Latest Date

{Fixed : MAX([Order Date])}


Latest Month

Date(DATETRUNC('month',[Latest Date]))


Then I create the calculated field for the last 6 months;


Last 6 Months

Date(DATEADD('month',-5,[Latest Month]))


Then I create a formula to always display the last 6 months;


6 Month Lookback

[Order Date]>=[Last 6 Months]

AND [Order Date] <= [Latest Month]


When I add this to the Filters Marks card, I make sure this is set to True.


There can be other ways you can create this calculated field. This is just one method.


If you wanted to pull the last 3 months, you can change the -5 to -2. It will pull the current month and the two months before. So if its December, it will pull data back to October. Change this to -3 if you want to pull in data back to September.


If you want to flip between the two, I recommend creating the following String Parameter.



Then create the following calculated field and place it on your Filters Marks card with the value of True.


Lookback Filter

IF [Look Back]="3 Month" then [3 Month Lookback]

ELSEIF [Look Back]="6 Month" then [6 Month Lookback]

END


Fellow Tableau Ambassador Chris McClellan brought up that you can have the look back period as a Integer Parameter, so your user can enter how many months they want to have for the time period.


Here is the Integer Parameter;



I displayed it so the user can enter the negative number of months;



I then created a calculated field that has this parameter embedded inside the formula;



Then I changed the True / False formula to this;


If you want to learn month over month, year over year calculations. Refer to my previous blog post here. You will see all of the different calculations on how to find current month to date data, prior month, prior year, etc.


162 views0 comments

Comments


bottom of page