SSRS - How to pull previous Friday with SSRS expression

Question

I'm needing help to pull the previous business days and excluding the weekends in SSRS. I use the following code, however this code doesn't look past the weekends.

Here's the code I normally use:

=DateAdd(DateInterval.Day
, Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
,DatePart(DateInterval.WeekDay, Today()) = 1, -2
,True, -1)
, Today())

Does anyone have any thoughts on how to write an SSRS expression to look past the weekends and pull only business days?

Do you mean you want the previous M-F business day of a given day? — Ross Bush, Apr 11 '17 at 19:26
No, I need to pull previous Friday and have it set in the report. Every Monday we need to pull Friday data. — BIReportGuy, Apr 11 '17 at 20:10
What do you mean by the code doesn't look past the weekends? Can you edit your question to include what you expect for a date range when the report is run on a Sunday, Monday, Tuesday, Wed., etc.? — R. Richards, Apr 11 '17 at 20:59
So, I have reports that the business runs every Monday, but they need to pull the data as of the previous bizday Friday. I have the expression set in the parameter to pull the previous business day, but it's only brings back Sunday (from the code above). Example: Monday 4/10...I need the expression to pull 4/7(prev biz day) Is there a way to write the expression to skip the weekends and only pull the previous biz day? — BIReportGuy, Apr 11 '17 at 22:29

score 1 · Answer 1 · answered Apr 12 '17 at 00:45

You may have a different first day of the week set (depending on region or how the server is configured). Explicitly setting FirstDayOfWeek in the DatePart function should give consistent results regardless of this setting:

=DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today(), FirstDayOfWeek.Sunday) = 2, -3
        ,DatePart(DateInterval.WeekDay, Today(), FirstDayOfWeek.Sunday) = 1, -2
        ,True, -1)
    , Today())

score 1 · Answer 2 · answered Apr 12 '17 at 01:51

If you want to default a date parameter to Friday when the report runs on a Monday (otherwise the default is the prior day), try this:

=DateAdd(DateInterval.Day, IIf(DatePart(DateInterval.Weekday, Today) = 2, -3, -1), Today)

This may be all you really need.

The following expression can be used to default a date parameter to the prior Friday, no matter what day of the week the report is run on. This can have some value for weekly reporting.

=DateAdd(DateInterval.Day, -(DatePart(DateInterval.Weekday, DateAdd(DateInterval.Day, 1, Today), 0, 0)), Today)

score 1 · Accepted Answer · answered Apr 12 '17 at 17:17

Continued exploring this and worked with others here...This is what we came up with to look to the previous business day and exclude the weekends when pulling the report on a Monday. This expression would be added to the date parameter.

=Switch(WeekdayName(DatePart("w", Today)) = "Wednesday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -2)    , Today()),

    WeekdayName(DatePart("w", Today)) = "Thursday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -2)    , Today()),

    WeekdayName(DatePart("w", Today)) = "Friday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -2)    , Today()),

    WeekdayName(DatePart("w", Today)) = "Saturday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -2)   , Today()),

    WeekdayName(DatePart("w", Today)) = "Sunday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -3)    , Today()),

    WeekdayName(DatePart("w", Today)) = "Monday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -4)    , Today()),

    WeekdayName(DatePart("w", Today)) = "Tuesday",DateAdd(DateInterval.Day
    , Switch(DatePart(DateInterval.WeekDay, Today()) = 2, -3
 ,DatePart(DateInterval.WeekDay, Today()) = 1, -2,True, -2)    , Today())

)

score 0 · Answer 4 · answered Dec 05 '18 at 22:11

0

This expression will also pull the previous business day, which I've been using lately. Just need to update your Date Parameter with this code.

=DateAdd("d",Switch(DatePart("w", Today) = 2,-3,DatePart("w", Today) = 1,-2,True,-1),Today)

answered Dec 05 '18 at 22:11

BIReportGuy

799
3
13
36

SSRS - How to pull previous Friday with SSRS expression

4 Answers4