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!
Do you need to use Tableau to compare values to an equivalent date from a prior year? For example, "How did sales from this Saturday compare to the same Saturday from last year?" For a restaurant (like The Melting Pot who I worked with on this problem!), comparing to the same weekday last year is crucial.
This is different than comparing to the exact same date from last year since the comparison is probably 364 days (52 weeks) ago. Additionally, this can be made complicated if there are any gaps in your data. For example, if your business doesn't record transactions on Saturdays, you can't simply use the LOOKUP function to go back 364 days because that would take you back too far. Additionally, if you apply a filter and "lose" days in your data set as a result, the LOOKUP approach will fail.
Check out this video and workbook for a full breakdown of how you can use the DATEDIFF function to ensure you're always comparing values across equivalent dates regardless of what data might be missing in your data set or worksheet.
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
Calculations
Date Calc:
DATE(
IF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 363 THEN [Order Date ]
ELSEIF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 727 THEN DATEADD('day',364,[Order Date ])
ELSEIF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 1093 THEN DATEADD('day',728,[Order Date ])
END
)
Year Label Calc:
IF [Order Date ] >= TODAY() THEN "Out of Scope"
ELSEIF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 363 THEN "Current Year"
ELSEIF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 727 THEN "Prior Year"
ELSEIF DATEDIFF('day',[Order Date ],(TODAY()-1)) <= 1093 THEN "Two Years Ago"
ELSE "Out of Scope"
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!