MDX-How do I calculate sales for 12 Month to date?
User Rating: / 9
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

Result:

 Order Count August 2003 1,759 September 2003 1,783 October 2003 1,779 November 2003 1,888 December 2003 2,272 January 2004 1,944 February 2004 2,030 March 2004 2,108 April 2004 2,127 May 2004 2,385 June 2004 2,374 July 2004 976 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

Result

Last 12 Mth Order Count
23,425

10 Sunday, 26 January 2014 10:31
Anann
Great use for the ClosingPeriod function. Small remark: it should be lag(11) els you get 13 months
9 Tuesday, 15 September 2009 20:52
Paul M
I'm a newbie with SSAS & with MDX. I'm trying to calculate an aggregate to-date that crosses years i.e. at each point in time (day), report the total to-date, so that e.g.
date count to-date
1/1/08 2 2
1/12/09 1 3
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
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
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
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