none
First and Last Day of the Week

    Question

  • I am trying to write a report and would like to set the dates like this.

    if the report is ran on a monday or tuesday i would like the report to do the last saturday to the last friday.

    for example if I ran it on

    8.19.2013 then the date range should be 08.10.2013 to 08.16.2013 that would also be the same if i ran it on 8.20.2013. 

    please help i have tried DateAdd and DatePart and can not seem to come up with the right combo.

    the last expression i just tried was

    DateAdd("d", -8, FirstDayOfWeek.System) and that gave an error message.

    Tuesday, August 20, 2013 7:43 PM

Answers

  • Try something like below code expressions:

    First Day of Last Week = DateAdd("d", (Weekday(Today()) + 7) * -1 , Today())
    Last Day of Last Week = DateAdd("d", (Weekday(Today()) + 1) * -1 , Today())

    Please first put these expressions in textboxes for testing and test every scenario. Play with above mentioned functions, I hope you would get desired result.

    Wednesday, August 21, 2013 12:34 AM

All replies

  • Try something like below code expressions:

    First Day of Last Week = DateAdd("d", (Weekday(Today()) + 7) * -1 , Today())
    Last Day of Last Week = DateAdd("d", (Weekday(Today()) + 1) * -1 , Today())

    Please first put these expressions in textboxes for testing and test every scenario. Play with above mentioned functions, I hope you would get desired result.

    Wednesday, August 21, 2013 12:34 AM
  • Hi Ann,

    As Samiullah posted, you can obtain the two expected date values using the two expressions (= DateAdd("d", (Weekday(Today()) + 7) * -1 , Today()) and = DateAdd("d", (Weekday(Today()) + 1) * -1 , Today())).

    Additionally, according to the description, do you want to specify the date range when the report runs on other weekdays rather than Monday or Tuesday? If you don’t want to specify any date range to filter the report date when the report runs on other weekdays, we can refer to the steps below:

    1.Right click the tablix and select Tablix Properties.

    2.Click the Filters tab, and add two filters (supposing the field name is StartTime) as follows:

    Expression: =IIf(weekday(today)=2 or weekday(today)=3,Fields!StartTime.Value,nothing)
    Operator: >=
    Value: =IIf(weekday(today)=2 or weekday(today)=3,DateAdd("d", (Weekday(Today()) + 7) * -1 , Today()),nothing)

    Expression: =IIf(weekday(today)=2 or weekday(today)=3,Fields!StartTime.Value,nothing)
    Operator: <=
    Value: = IIf(weekday(today)=2 or weekday(today)=3,DateAdd("d", (Weekday(Today()) + 1) * -1 , Today()),nothing)

    If you need more assistance, please feel free to let me know.

    Regards,
    Heidi Duan

    Wednesday, August 21, 2013 8:22 AM
  • thank you so much this worked great.
    Thursday, August 22, 2013 3:07 PM
  • thank you, i am going to stick with the above equation just in case we want to run it any other time.

    Thursday, August 22, 2013 3:08 PM
  • hello could you please tell me how I can do the beginning and end of the previous month?
    Tuesday, July 15, 2014 3:26 PM