I am a newbie to mdx and struggling with this issue. My Dimension is structure like this:
Dimension: Business Time
Hierarchy: Business Time
Levels: Year, Month,Week,Date
Measures: Net Value
I am trying to compare sales across two years on day of the week in a particular week ie Tuesday week 29 2011 and Tuesday week 29 2010. I can get the date but unfortunately cannot get weekday as its not in the dimension.
Please advise as i have been stuck on this for two days now.
I found this information: http://msdn.microsoft.com/en-us/library/ms146084.aspx
This query gives you the week of year:
with member weekofyear as datepart("ww",Now())select weekofyear on 0 from [cubename]
This query gives you the day of week:
with member dayofweek as datepart("w",Now())select dayofweek on 0 from [cubename]
Let me know if that solves your problem.