Wednesday, January 2, 2013

Some more SSRS Expressions Collection

Month over month



=DateAdd("d",-(Day(today)-1), Today)

Week over week

=DateAdd("ww",-1, Today)


=DateAdd("d",-7, Today)



Year over Year

=DateAdd("yyyy",-1, Today)



Notice in the YTD and WTD expression I’m calling a DateInterval function that is actually a Visual Basic function but does not require you do add anything to the custom code section of your report.

"d",-DatePart(DateInterval.DayOfYear,DateAdd("yyyy",-1, Today),0,0)+1,DateAdd("yyyy"
,-1, Today))

First day of week (monday):
=DateAdd("d", -(WeekDay(Today(),2))+1, Today())

Next monday:
=DateAdd("d", 7-(WeekDay(Today(),2))+1, Today())

First day of month:

First day of next month:

First day of year:

First day of next year:

First Date of last month

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

=Today.AddDays(1- Today.Day).AddMonths(-1)

Last date of last month

=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))

=Today.AddDays(-1.0 * Today.Day)

First date of current month

=DateSerial(Year(Now()), Month(Now()), 1)

=Today.AddDays(1 - Today.Day)

Last date of current month

=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))

=Today.AddDays(-1 * Today.Day).AddMonths(1)

Yesterday's date 

=DateAdd("d", -1, Today)


Tomorrow's date 

=DateAdd("d", 1, Today)


* Monday of current week

=DateAdd("d", 2 - DatePart("w",Today) , Today)

=Today.AddDays(1- Today.DayOfWeek)

* Friday current week

=DateAdd("d", 6 - DatePart("w",Today) , Today)

=Today.AddDays(5- Today.DayOfWeek)

First day of current year

=DateSerial(Year(Now()), 1, 1)

=Today.AddDays(1-  Today.DayOfYear)

Last day of current year

=DateSerial(Year(Now()), 12, 31)

=Today.AddDays(-1 *  Today.DayOfYear).AddYears(1)

First day of current quarter

=DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)

=Today.AddDays(1-  Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) ) -3)

Last day of current quarter

=DateAdd("d",-1,DateAdd("q",1,DateSerial(Year(Now()), (3*DatePart("q",Now()))-2, 1)))

=Today.AddDays(1-  Today.DayOfYear).AddMonths((3 * (((Today.Month-1) \ 3) + 1) )).AddDays(-1)

--Expression to get the 1st day of the previous month (aka Start Date) 
DateSerial(iif( Month(DateTime.Now)=1, Year(DateTime.Now)-1, Year(DateTime.Now)), iif( Month(DateTime.Now)=1, 12, Month(DateTime.Now) - 1), 1)

--Expression to get the 1st day of the current month (aka End Date)
DateSerial(Year(DateTime.Now), Month(DateTime.Now),1)

--Expression to get the 1st day of the next month
DateSerial(iif( Month(DateTime.Now)=12, Year(DateTime.Now)+1, Year(DateTime.Now)), iif( Month(DateTime.Now)=12, 1, Month(DateTime.Now) + 1), 1)

1 comment:

Alexander Klim said...

Thanks! This saved for me a lot of thinking time.