We have a really typical looking Date table. However, we are going to be drawing some pretty charts summarized by weeks, and our business defines “end of week” at Saturday. So, we need a new column in our Date table that stores this “Week Ending” date for each row.
The first thought to occur to me was “well, for each Year&WeekOfYear, I just want to grab the max date”. That sounded easy enough… EARLIER() no longer scares me…
Calendar[Year] = EARLIER(Calendar[Year]) &&
And for a brief moment, I felt very proud of my calculated column. There were lots of rows that were totally correct. Most of them in fact! However, closer inspection found two problems:
1) 4/24 showed up as a WeekEnding, because my calendar table stopped there, even though it wasn’t a Saturday. I wasn’t feeling horrible at that point… that felt solvable.
2) 12/31 showed up as WeekEnding because of the year change. That felt harder to solve.
Luckily, I had a brief moment of clarity, and recalled Rob saying just yesterday “Well, dates are just integers… that is why you can subtract 1 to get to yesterday’s date”. Hmmm, brain churning, thinking, … typing!
You can see the results at right. Tell me they aren’t super sexy and totally promising!
All we need to do is that MOD() value, which is just a remainder after an integer division, to add an appropriate number of days to each row!
In my case (week ending Saturday), I want to add 0 days on Saturdays, 1 day on Fridays, 2 days on Thursday, etc.
And my magic calculated column ended up being:
MOD(Calendar[Dates]-1, 7) + 6
I am not including a final picture of the correct results. It’s my little test to see if you trust me. Enjoy!