July 12, 2014 at 4:07 pm
Hi,
I have the Date].[Year - Week - Date].[Week] hierarchy and now i want sales of previous week and current week for comparision based on date passed by the user. for Instance, if the date entered is 12/07 than it should calculate total sales of current week getting data uptil 07/07 and previous week 29/06. Can you please help?
July 13, 2014 at 6:55 am
Try and play with something like below.
WITH MEMBER [Measures].[YTD untill previous week] AS
Sum(
YTD(Parallelperiod([Date].[Calendar Weeks].[Calendar Week],1,[Date].[Calendar Weeks].[Calendar Week].&[10]&[2008])),
[Measures].[Internet Sales Amount]
)
SELECT
{
[Measures].[Internet Sales Amount],
[Measures].[YTD untill previous week]
}
ON COLUMNS,
[Date].[Calendar Weeks].[Calendar Week].&[10]&[2008]
ON ROWS
FROM [Adventure Works]
--
Of course for the sake of the exercice 10 is used. For reporting purpose you should use currentmember.
Hope this helps. You can do this for weeks/days etc
July 13, 2014 at 7:29 am
Thank you but I have Date as a parameter so user can pass any date and based on it, I need to calculate current week and previous week? Can you suggest how to do that
July 13, 2014 at 7:36 am
When I have parameter set in the MDX query, how to test it in mquery analyser ..
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply