SSRS Expression First Day of of First Week of Current Year

0
votes

Everyone, I have a question that has stumped me for a day and can't figure out. What I am looking for is a formula in SSRS Expression that will tell me what the date is for the first day of the first ISO week of the current year.
For Example:

  • 2014 would yield: 12/30/2013. The reason for this would be that the first ISO week of the 2014 year is from (12/30/2013) - (01/05/2014).
  • 2013 would yield: 12/31/2012

I would appreciate any help anyone? Thanks,

1
reporting-services

1 Answers

2
votes

You can use this function:

Public Function dtFirstDayOfISOYear(ByVal intYear As Integer) as Datetime

    'the first week of a ISO year is the week that contains the first Thursday of the year (and, hence, 4 January)
    Dim intDayOfWeek As Integer = CInt(New DateTime(intYear, 1, 4).DayOfWeek)

     'ISO weeks start with Monday
    If intDayOfWeek < DayOfWeek.Monday Then intDayOfWeek = intDayOfWeek + 7

    Return DateAdd(DateInterval.Day, -intDayOfWeek + 1, New DateTime(intYear, 1, 4))

End Function

And call it using an Expression like this:

=Code.dtFirstDayOfISOYear(2014)

You can also use a standalone Expression like this:

=DateAdd("d", (-1) * (CInt(New DateTime(2014, 1, 4).DayOfWeek) + IIf(CInt(New DateTime(2014, 1, 4).DayOfWeek) < DayOfWeek.Monday, 7, 0)) + 1, New DateTime(2014, 1, 4))