SSAS FAQ MDX FAQ MDX-How do I calculate sales for 12 Month to date?
MDX-How do I calculate sales for 12 Month to date?
User Rating: / 1
PoorBest 
Written by Vidas Matelis   
Friday, 08 June 2007 02:44

Q: How do I calculate sales for 12 Month to date in MDX?

A: We have to assume that your date dimension last member represents last month. Then to get last 12 month we can use Lag function. Example:

SELECT [Measures].[Order Count] ON 0
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]) ON 1
FROM [Adventure Works]

Result:

 Order Count
August 20031,759
September 20031,783
October 20031,779
November 20031,888
December 20032,272
January 20041,944
February 20042,030
March 20042,108
April 20042,127
May 20042,385
June 20042,374
July 2004976
August 2004(null)

Now to get sum of sales query could be :

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM(
ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
, [Measures].[Order Count]
)
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works]

Result

Last 12 Mth Order Count
23,425

< Prev   Next >
 
Comments (8)
8 Friday, 28 March 2008 14:18
Raaz
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Year],
[Date].[Calendar].[Year].[@Year]
),
[Measures].[Sales Amount])

I defined the parameter in dataset whose value i set to the report parameter, It is not working Above is what i modified when i defined parameter. Can any body help me plz how to parameterized year in above code?
Thanks
7 Thursday, 27 March 2008 20:41
Raaz
Hi,
Aggregate(
PeriodsToDate(
[Date].[Calendar].[Year],
[Date].[Calendar].[Year].[Year 2007]
),
[Measures].[YourMeasure])
I used this code to calculate the YTD Measure using Add Calculated tool, It worked fine but when i used it in report using expression just like other Field values, It gives me Output in "xml" kinda form instead of Values. In query pane it works fine. Any body have Idea how i will use this calculated Measure in report?
Thanks
6 Monday, 24 March 2008 23:32
admin
Very first query in this FAQ shows values by rows. To show months on columns just switch in the first query to different Axis (0 with 1). To get this working with Reporting Services as parameters please read Articles section that is related to Reporting Services. There are a lot of good examples there how to do that.
Regards
5 Monday, 24 March 2008 16:42
Jhon
You are Right vidas, Actually what my report requirement is that, it should take month and year input from user and show all the 12 months [Measure value] accordingly.I know how to show One month name and the its related value, but m not sure how to do that showing all month names on the columns and show thier related values underneeth. Can any body help me in that? Thanks in Advance
4 Monday, 24 March 2008 16:17
admin
I am assuming you would want to use this in Excel, Reporting Services or other client. In such case just create calculated member in the cube Calcuation tab. I do not know other way for that.
3 Monday, 24 March 2008 16:10
Jhon
Yea It worked, Thanks Vidas,

Is there any way that we can use just expressions for this instead of MDX query?
2 Monday, 24 March 2008 15:36
admin
Jhon, try something like this:

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM(
ClosingPeriod([Date].[Month].[Month], [Date].[Month].[All]).Lag(12)
: ClosingPeriod([Date].[Month].[Month], [Date].[Month].[All])
, [Measures].[YourMeasure]
)
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Your Cube]
1 Monday, 24 March 2008 10:18
Jhon
My Date Hierarchy is Like this: [Date].[Month].[All] How i would edit the above code which will work for me to calculate 12 months Sales? Thanks
Strategy Companion