CODEWITHSUNDEEP
datereporting-servicesformula
lizfrinko
lizfrinko

Reputation: 25

SSRS - How to show rolling 6 months where the start date is always a Saturday?

I need help on calculating my start date for my report date parameters.

The end date will always be the last Sunday, here: =DateAdd("d", 1 - WeekDay(Today(), 1), Today())

What I need help with is how to write a formula to go back 6 months from today and pick the 1st Saturday in that range..

Thanks in advance.

Upvotes: 0

Views: 626

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

Assuming your start day is a Sunday then you can use this...

=DATEADD(
        DateInterval.Day, 
        7 - WEEKEDAY( DATEADD(DateInterval.Month,-6,Today()), FirstDayOfWeek.Sunday),  
        DATEADD(DateInterval.Month,-6,Today())
        )

This works as follows

WEEKEDAY( DATEADD(DateInterval.Month,-6,Today()), FirstDayOfWeek.Sunday)

Takes today's date, subtracts 6 months and then finds out what daynumber that is. Running that today (2018-11-08) gives use (2018-05-10) which is a Thursday, this is day number 5

Saturdays are day number 7 (if your first day of week is a Sunday). As there can be no higher number than 7 we can do a simple subtraction of 7 minus the day number we landed on (from above) which gives us a required adjustment of 2 days.

Finally the outer DATEADD function simply says add our calculated 2 days to the date 6 months ago.

Hope that makes sense!?

If the first day of the week is not a Sunday for you then you may have to do some Mod% calc on the second argument to calculate the correct number of days to adjust by.

Upvotes: 1

Related Questions