CODEWITHSUNDEEP
reporting-servicesexpressionssrs-2012
Sebastian
Sebastian

Reputation: 4811

SSRS Expression to near Monday of last Year

In SSRS how can I write an expression to find the Monday of Last year for a date Example: If I choose 9/26/2016 which is a Monday , I have to find 9/26/2015 is Monday or not . In this case 9/26/2015 Is Saturday , so I have to set the date parameter as the nearest Monday which is 9/21/2015
How can I do it via an SSRS expression 

 =DATEADD(DateInterval.Year,-1,Parameters!BeginDate.Value)

This will helps to find the Last Year same date , but how can I change to make it to select the Monday of last Year

Upvotes: 0

Views: 66

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21768

=DateAdd
(
    DateInterval.Day
    , 2 - weekday(dateadd(DateInterval.Year,-1,Parameters!BeginDate.Value))
    , dateadd(DateInterval.Year,-1,Parameters!BeginDate.Value)
)

To break the DateAdd function down... The first argument is simply telling it to adjust by a number of days The next argument gets the weekday for the parameter date minus a year, we then take this number FROM 2 (which is the weekday value for a Monday) so if the parameter date last year was a Thursday (5) we would get 2-5 which gives us -3 so we'll take three days of our date. The final argument is the parameter date minus a year which is the date we want to subtract (in this case) 3 days from.

NOTE If you system setting are not default the weekdays returned may not be the same so check and adjust the , 2-(... part accordingly, set it to whatever Monday evaluates to.

Upvotes: 1

Related Questions