MDX. How to return this week's time member.

Innovative Uses
1

Using MDX, how can I return this week’s and last week’s time members? I use a time hierarchy that goes down to the day.

This MDX expression returns today’s time member.
Filter([Time].[Default].[Day].Members, [Time].[Default].CurrentMember.Properties(‘StartPeriod’, TYPED) = CDATE(FORMAT(dateadd(“D”,0,now()),“MM/d/yyyy”)))

2

Hi Jeff,

Given that you have a time dimension that goes down to the day, the MDX named set formula (in the time dimension) that I would use to pull the current date would be
StrToMember("[Day].[Default].&[" + Format(Now(), “yyyy”) + “D” + Format(DatePart(“y”, Now()), “000”) + “]”)

On your question, by this week and last week, to clarify, taking current date, are you looking for 9/12-9/25?

Cheers,
Navin

3

based on today’s date, I would like to pull in just last week’s results (9/12 - 9/18) and this week’s results (9/19 - 9/25). Separate values. Thanks.

4

Hi Jeff,

For current week I would use the below formula
StrToMember("[Day].[Default].&[" + Format(Now(), “yyyy”) + “D” + Format(DatePart(“y”, Now())-Weekday(Now())+1, “000”) + “]”)
:
StrToMember("[Day].[Default].&[" + Format(Now(), “yyyy”) + “D” + Format(DatePart(“y”, Now())-Weekday(Now())+7, “000”) + “]”)

For prior week, I would use:
StrToMember("[Day].[Default].&[" + Format(Now(), “yyyy”) + “D” + Format(DatePart(“y”, Now())-Weekday(Now())-6, “000”) + “]”)
:
StrToMember("[Day].[Default].&[" + Format(Now(), “yyyy”) + “D” + Format(DatePart(“y”, Now())-Weekday(Now()), “000”) + “]”)

These formulas, in a named set, would create a list of 7 days. Please note that the [Day] is the time dimension in this case.

Hope this helps,
Navin

1 Like