# DAX – Week Ending Date Calculation

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!

Read more on our blog

Get in touch with a P3 team member

• Hidden
• Hidden
• This field is for validation purposes and should be left unchanged.

This field is for validation purposes and should be left unchanged.

### Related Content

#### Exciting Improvements to Power BI’s Export to Excel

It is a standing joke in the analytics industry that “Export to

Read the Blog

#### The case of the disappearing Field Parameters: SOLVED

The Case: The new Field Parameters feature from Microsoft had been added

Read the Blog

#### Completing the Set Up: Field Parameters Using Tabular Editor

May 2022’s release of Power BI Desktop is the best releases we’ve

Read the Blog

#### Calculation Groups to the Rescue!

o set the stage, I need you to travel back in time

Read the Blog