I am trying to resolve an issue involving comparing Year Over Year data results by day, and cant seem to quite figure it out.
I want to be able to accurately compare Year Over Year results by a specific day - Historically we have been calculating this as [Years Back] * 364 to obtain the number of days between the two dates, but as the yearly difference becomes greater than 7 it becomes incorrectly calculated.
I attached a picture here showing the anomaly where there is not a constant difference YoY and between two dates. 2015 is not a leap year.
What is the best way to calculate any given dates past equivalent date? If i have two calendar tables, what do I match on? (I can get any of the following fields to match Date, Day of Week Number, Day of Month Nomber, Week Number of Month, Week number of Calendar Year etc.) For example, need to input date, and have an output of past year equivalent Like So:
Below scenario is for labor day
Given Date | Past Year equivalent 20190902 | 20180903 20190902 | 20170904 20190902 | 20160905 20190902 | 20150907 20190902 | 20140901