Custom Date Filter for Aggregated and Non-Aggregated Measures in Tableau

By: Eric Parker

Eric Parker lives in Seattle and has been teaching Tableau and Alteryx since 2014. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email. You can follow him on LinkedIn or sign up for a Tableau Office Hour to work with him directly!

Creating custom date filters in Tableau takes some creativity and ingenuity. They can be especially tricky when they are used to control both aggregated and non-aggregated measures. Tableau is particular about how aggregated formulas are written.

If you want to create a custom date filter in Tableau that controls both aggregated and non-aggregated measures but you're struggling to write the formulas without errors, check out this video for some helpful tips!

Need help with Tableau? Sign up for an office hour to work with Eric! You can also check out our upcoming classes for a Tableau deep dive!

Related Resources:

• Walk Along Tableau Workbook

• Complete Tableau Workbook

• Layout Containers

• Business Dashboard Formatting

• Dynamic Parameters in Tableau

• Calculate and Filter to MAX Date in Dataset

• Fiscal Year to Date vs. Prior Fiscal Year to Date

• Fiscal Quarter to Date vs. Prior Fiscal Quarter to Date

• Aggregated vs. Non-Aggregated Tableau Calculation Errors

Other Date Filtering Videos

• Dynamic Date Filter in Tableau

• Dynamic Start and End Date Filter in Tableau

• Custom Date Filter in Tableau

• Build Selected vs. Comparison Date Period Filter in Tableau

Formulas:

Selected Period T/F

CASE [Date Parameter]

WHEN "Year to Date" THEN DATEDIFF('year',[Order Date ],TODAY()) = 0

WHEN "Last Year" THEN DATEDIFF('year',[Order Date ],TODAY()) = 1

WHEN "Last 12 Months" THEN DATEDIFF('month',[Order Date ],TODAY()) <= 11

WHEN "Quarter to Date" THEN DATEDIFF('quarter',[Order Date ],TODAY()) = 0

WHEN "Last Quarter" THEN DATEDIFF('quarter',[Order Date ],TODAY()) = 1

WHEN "Last 90 Days" THEN DATEDIFF('day',[Order Date ],TODAY()) <= 89

WHEN "Month to Date" THEN DATEDIFF('month',[Order Date ],TODAY()) = 0

WHEN "Last Month" THEN DATEDIFF('month',[Order Date ],TODAY()) = 1

WHEN "Last 30 Days" THEN DATEDIFF('day',[Order Date ],TODAY()) <= 29

END

Prior Period T/F

CASE [Date Parameter]

WHEN "Year to Date" THEN DATEDIFF('year',[Order Date ],TODAY()) = 1 AND DATEPART('dayofyear',[Order Date ]) <= DATEPART('dayofyear',TODAY())

WHEN "Last Year" THEN DATEDIFF('year',[Order Date ],TODAY()) = 2

WHEN "Last 12 Months" THEN DATEDIFF('month',[Order Date ],TODAY()) <= 23 AND DATEDIFF('month',[Order Date ],TODAY()) >= 12

WHEN "Quarter to Date" THEN DATEDIFF('quarter',[Order Date ],TODAY()) = 1 AND DATEADD('quarter',1,[Order Date ]) <= TODAY()

WHEN "Last Quarter" THEN DATEDIFF('quarter',[Order Date ],TODAY()) = 2

WHEN "Last 90 Days" THEN DATEDIFF('day',[Order Date ],TODAY()) <= 179 AND DATEDIFF('day',[Order Date ],TODAY()) >= 90

WHEN "Month to Date" THEN DATEDIFF('month',[Order Date ],TODAY()) = 1 AND DAY([Order Date ]) <= DAY(TODAY())

WHEN "Last Month" THEN DATEDIFF('month',[Order Date ],TODAY()) = 2

WHEN "Last 30 Days" THEN DATEDIFF('day',[Order Date ],TODAY()) <= 59 AND DATEDIFF('day',[Order Date ],TODAY()) >= 30

END

Prior Period Label

CASE [Date Parameter]

WHEN "Year to Date" THEN "Prior Year to Date"

WHEN "Last Year" THEN "Prior Year"

WHEN "Last 12 Months" THEN "Prior 12 Months"

WHEN "Quarter to Date" THEN "Prior Quarter to Date"

WHEN "Last Quarter" THEN "Prior Quarter"

WHEN "Last 90 Days" THEN "Prior 90 Days"

WHEN "Month to Date" THEN "Prior Month to Date"

WHEN "Last Month" THEN "Prior Quarter"

WHEN "Last 30 Days" THEN "Prior 30 Days"

END

Want to keep learning foundational skills in Tableau? Sign up for our weekly newsletter so you never miss new blog posts, videos and events!