SSRS Month over Month Date Expression beginning with Previous business day

Question

The business requirement is to have a report with a month over month date starting with the previous business day. How do I achieve?

For month over month - my expression is:

=dateadd("m",-3,Today)

For previous business day - my expression is:

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

How can I combine these into one expression so that the month over month starts with the previous business business day and not today's date?

Thanks,

score 1 · Accepted Answer · answered Oct 24 '16 at 15:45

You can use your previous business day expression in the start business day expression:

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

However the outer DATEADD function could produce a date which is not business day and your query will return rows from that date, if your datasource doesn't match dates in non-business days that is not a problem, the problem comes when you have non-business dates and you don't want to report it.

For simplicity I'd create a Hidden parameter (check the Hidden property radio control in Parameter properties) called ThreeMonthsPreviousDay, set it to Date/Time data type and use the the below expression in the Default Values property.

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

Now you can get the Business Date 3 months before the previous business day so use:

=DateAdd(DateInterval.Day,
  Switch(
    DatePart(DateInterval.WeekDay, Parameters!ThreeMonthsPreviousDay.Value) = 2, -3,
    DatePart(DateInterval.WeekDay, Parameters!ThreeMonthsPreviousDay.Value) = 1, -2,
    True, 0), 
 Parameters!ThreeMonthsPreviousDay.Value)

Let me know if this helps.

SSRS Month over Month Date Expression beginning with Previous business day

1 Answers1