Reputation: 4811
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
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