Guess Post by **Scott Senkeresty** at Tiny Lizard

Just a quick and practical tip today.

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…

[WeekEnding] =

CALCULATE(MAX(Calendar[Dates]),

FILTER(ALL(Calendar),

Calendar[Year] = EARLIER(Calendar[Year]) &&

Calendar[WeekOfYear] =

EARLIER(Calendar[WeekOfYear])

)

)

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!

[ModTest] =

MOD(Calendar[Dates], 7)

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:

=Calendar[Dates] –

MOD(Calendar[Dates]-1, 7) + 6

Depending on the particular day of the week you want to use for your WeekEnding, that -1 in the equation will change. To save you the trouble I have created the very fancy chart at left.

I am not including a final picture of the correct results. It’s my little test to see if you trust me. Enjoy!

I used that in a couple of data models, but I never noticed the error when year change.

Thanks

I used existing date functions and worked out well for me. Here is the formula:

=Calendar[Dates]+(7-WEEKDAY(Calendar[Dates]))

this is the formula for Saturday as your weekending day

As we already know 7 days a week, weekday() function returns integer value represents the day of week.

when i used this i got the weekednigs correctly even my table stopped in the middle of the week.

Thanks

That seems far easier to understand! Thanks for posting it Karthik! 🙂

That works really well! Thanks Karthik

I’ve had to add a Week Ending column and I can use this formula in Excel and SSAS Tabular cube.

For Week ending Fridays…. =date(year([YourDateColumn]),Month([YourDateColumn]),Day([YourDateColumn]))+7 – Weekday(date(year([YourDateColumn]),Month([YourDateColumn]),Day([YourDateColumn]))+1)

For Week ending Sundays… =date(year([YourDateColumn]),Month([YourDateColumn]),Day([YourDateColumn]))+9 – Weekday(date(year([YourDateColumn]),Month([YourDateColumn]),Day([YourDateColumn]))+1)

For week ending on Sunday, I came up with this:

=[YourDateColumn]+MOD(8-WEEKDAY([YourDateColumn]),7)

This is considering that the week commences on a “Sunday”. However, for a week commencing on “Monday” I have modified the formula as follows:

=dimDate[Date]+MOD(7-WEEKDAY(dimDate[dimWeekDay]),7)

Thank you! 🙂

Chris – you’re a star! Cheers, Tony

Week Start & End Date From Date

WeekStartDate = [DateColumn] – (WEEKDAY([DateColumn])-1)

WeekEndDate = [DateColumn] + (7-WEEKDAY([DateColumn]))

Thanks this also works for me with the Friday as the weekending. Saturdays = ‘Calendar'[Date]+MOD(6-WEEKDAY(‘Calendar'[Date]),7).