MDX to get last Sunday and Saturday

Question

I built a SSRS 2005 report on a SSAS 2005 cube. The report has start date and end date parameters from Time dimension. I need to set the default values to be last Sunday and last Saturday separately. (financial week is from Sunday to Saturday)

E.g.

Start date: [Time].[Day].&[20140309] End date: [Time].[Day].&[20140315]

How can I do it dynamically? I mean for this week is above dates, but for next week, it should be 16 March, 22 March. I know how to do it in T-SQL, which will involve some calculation with system date, but MDX?

Do you want to have the last Saturday and the last Sunday contained in the ` [Time].[Day]` hierarchy? Or the Saturday before and up to today, and the Sunday of today or the next one after today? — FrankPl
@FrankPl The Time hierarchy has financial year, financial week, and day. You mean I put last Sunday, last Saturday in for each row? — thotwielder
No, my question was "how do i determine what the 'last' Saturday/Sunday is"? Can I assume that you need the last Saturday and Sunday contained in the time dimension? Do I have to take the current date into account? — FrankPl
@FrankPl I mean for this financial week (16 - 22 Mar), every time the report opens it should have default start date and end date to 20140309 and 20140315. Or for next financial week (23 - 29 Mar), every time the report opens it should have default start date and end date to 20140316 and 20140322. — thotwielder

FrankPl FrankPl · Accepted Answer · 2014-03-19T18:18:51

You can use some VBA Date functions available in MDX:

StrToMember('[Time].[Day].&['
            + Format(DateAdd('d', - DatePart('w', Now(), 1), Now()), 'yyyyMMdd')
            + ']'
           )

should give you the last Saturday before today, and

StrToMember('[Time].[Day].&['
            + Format(DateAdd('d', - DatePart('w', Now(), 1) - 6, Now()), 'yyyyMMdd')
            + ']'
           )

the last Sunday before that.

The second argument of DateAdd('d', ...) is the number of days to add. And as this is negative here, we go back in time that many days. DatePart('w', ...) returns the weekday number (Sunday = 1, Monday = 2, ...). Thus, if you subtract "weekday number" days from today, luckily you are already at last Saturday. And subtracting six more days, you arrive at the last Sunday before that.

MDX to get last Sunday and Saturday

1 Answers