I had a recent request to change the default date range for a report to be the previous week Mon-Sun.

With SQL, you can use the current week and DATEADD into the previous week, like this:

IF (@StartDate IS NULL)
    SET @StartDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 1, 0)
IF (@EndDate IS NULL)
   SET @EndDate = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)

SSRS uses VB.Net as it’s expression language, and as a result I couldn’t directly translate the functions as the behavior with the DATEADD using a week interval returns the wrong date.

Instead I wound up using these functions instead. Note the use of the multiplication by –1. WIthout this, the calculation would only be correct on Mondays, and we need it to work as the week progresses further away from Monday each day.

=today.AddDays(((today.DayOfWeek - DayOfWeek.Monday)*-1)-7)
=today.AddDays(((today.DayOfWeek - DayOfWeek.Monday)*-1)-1)