SSRS

SSRS Date Expressions Cheat Sheet

Below is a list of several date related SSRS expressions I use in SSRS Reports.  Leave a comment if you need help with a specific Date expression that is not covered here.  As always, you can also reach me via connecting on LinkedIn or in the PowerBI Community. Feel free to leave an endorsement on my LinkedIn if any of my material helps.

Weekly Expressions

Week over week

=DateAdd(“ww”,-1, Today)

or

=DateAdd(“d”,-7, Today)

WTD

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

First day of week (monday):

=DateAdd(“d”, -(WeekDay(Today(),2))+1, Today())

Next monday:

=DateAdd(“d”, 7-(WeekDay(Today(),2))+1, Today())

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)

End of this week

=DateAdd(“d”, 8 – Weekday(Today), Today).ToString(“dd/MM/yyyy”)

Start of the week (2 weeks ago)

=DateAdd(“d”, -(Weekday(Today)+12) , Today).ToString(“dd/MM/yyyy”)

Monthly Expressions

Month over month

=DateAdd(“m”,-1,Today)

MTD

=DateAdd(“d”,-(Day(today)-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 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)

Return Current Month Name

=MonthName(Month(Today()))

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)

 

Yearly Expressions

Year over Year

=DateAdd(“yyyy”,-1, Today)

YTD

=DateAdd(“d”,-DatePart(DateInterval.DayOfYear,Today,0,0)+1,Today)

=DateAdd(

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

,-1, Today))

First day of year:

=DateSerial(YEAR(Today()),1,1)

First day of next year:

=DateSerial(YEAR(Today())+1,1,1)

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)

Author


Avatar