Wednesday, January 2, 2013

Some more SSRS Expressions Collection



Month over month

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

MTD

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

Week over week

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

or

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

WTD

=DateAdd("d",-DatePart(DateInterval.WeekDay,Today,0,0)+1,Today)

Year over Year

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

YTD

=DateAdd("d",-DatePart(DateInterval.DayOfYear,Today,0,0)+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.


=DateAdd(
"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:
=DateAdd("d",1-DatePart("d",Today()),Today())

First day of next month:
=dateadd("m",1,DateAdd("d",1-DatePart("d",Today()),Today()))

First day of year:
=DateSerial(YEAR(Today()),1,1)

First day of next year:
=DateSerial(YEAR(Today())+1,1,1)


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)

=Today.AddDays(-1)

Tomorrow's date 

=DateAdd("d", 1, Today)

=Today.AddDays(1)

* 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.