Round 2: Calculating Previous Year Marketing Calendar Values with DAX

Written by Dan English

Tuesday, 12 October 2010 23:53

So a couple of weeks back I did the posting Calculating Previous Year Marketing Calendar Values with DAX where I talked about a solution for getting at previous (last) year values in DAX with Marketing (Retail) Calendars. For the most part the solution worked, but as I looked at the results a bit closer I noticed that it eventually broke down and failed. Why is this?

Well the answer should have been obvious and I have ran into this before, but eventually these 445, 454, 544, or 13 period calendars run into a small issue that they need to deal with. The issue is that these calendars only deal with 364 days (13 periods x 4 weeks x 7 days = 364 days). So there is an extra day that we eventually need to deal with and then we run into the leap year and all of this eventually creates the mysterious week 53!