Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

YTD MDX Question Expand / Collapse
Author
Message
Posted Thursday, September 16, 2010 3:10 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 2, 2012 7:17 AM
Points: 160, Visits: 27,939
I have the following MDX calculation in my SSAS cube that returns Prior YTD Sales based on the current date.

Sum(YTD(ParallelPeriod([Calendar].[Year-Quarter-Month-Week-Day].[Year],1,[Calendar].[Year-Quarter-Month-Week-Day].CurrentMember)),[Measures].[Net Sales])

This will return the YTD sales for prior year, however I need a calculation to return the total sales for the entire prior year.

(based on today's date)
Example: Current YTD sales : $5,000,000
Prior YTD sales : $5,100,000
Prior Year Sales: $8,000,000 (this is the calc I can't figure out)

Any help would be greatly appreciated...
Post #987767
Posted Thursday, September 16, 2010 11:46 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
randy.jaye (9/16/2010)
I have the following MDX calculation in my SSAS cube that returns Prior YTD Sales based on the current date.

Sum(YTD(ParallelPeriod([Calendar].[Year-Quarter-Month-Week-Day].[Year],1,[Calendar].[Year-Quarter-Month-Week-Day].CurrentMember)),[Measures].[Net Sales])

This will return the YTD sales for prior year, however I need a calculation to return the total sales for the entire prior year.

(based on today's date)
Example: Current YTD sales : $5,000,000
Prior YTD sales : $5,100,000
Prior Year Sales: $8,000,000 (this is the calc I can't figure out)

Any help would be greatly appreciated...


any specific reason for not implementing...
SELECT [MEASURES].[SALES] ON 0
WHERE [DATE DIMENSION SLICE]=YEAR


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #987899
Posted Friday, September 17, 2010 9:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 2, 2012 7:17 AM
Points: 160, Visits: 27,939
I am trying to build a calculated member in a SSAS cube. I am relatively new to this process so I may not be aware of all the techniques.

The vast majority of our reporting and analysis is ver specific to a "day". And from the "day" being reported against or analyzed, we need an easy way to provide a calculated measure that provide YTD, Prior YTD,etc. based on day the user selects. I am just not clear on how to determine the Total Prior Year Sales based on a supplied date. For example, if user (or report) uses a day of 9/20/2010 then I need to total sales for 2009.

I hope this makes sense. I guess you could call me a newbie.
Post #988322
Posted Friday, September 17, 2010 11:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
randy.jaye (9/17/2010)
I am trying to build a calculated member in a SSAS cube. I am relatively new to this process so I may not be aware of all the techniques.

The vast majority of our reporting and analysis is ver specific to a "day". And from the "day" being reported against or analyzed, we need an easy way to provide a calculated measure that provide YTD, Prior YTD,etc. based on day the user selects. I am just not clear on how to determine the Total Prior Year Sales based on a supplied date. For example, if user (or report) uses a day of 9/20/2010 then I need to total sales for 2009.

I hope this makes sense. I guess you could call me a newbie.


Jaye,

The CURRENTMEMBER attribute will not help you to get the Total yearly sales
I suppose you will have to fire an all together new MDX for total sales, as mentioned above


Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #988673
Posted Saturday, September 18, 2010 1:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 3:28 AM
Points: 1,044, Visits: 1,839
WITH MEMBER [MEASURE_NAME] AS
AGGREGATE
(
PERIODSTODATE
(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CurrentMember
),
([Measures].[Sales Amount])
)

Note: This Measure will give you a cumulative value of total sales till current value



Regards/Raunak
Now a member of Linkedin

Please visit the all new Performance Point Forum
Please visit the all new Data Mining and Business Analytics Forum
Post #988683
Posted Wednesday, September 3, 2014 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 12:52 PM
Points: 1, Visits: 3
I am a newbie also and have been getting along fine calculating YTD information but now they have asked me to come up with a Prior Year End number and am having trouble figuring out the code. I found your post and it appears as though you were trying to figure out the same issue. I am wondering if you ever got a solution to your problem and if so if you can forward it on to me.
What I am trying to find is this. The total sales for 2013 where today is 08/31/2014. I can find YTD for 08/31/2014 and YTD for 08/31/2013, but finding YTD for 12/31/2013 has me stymied. Thanks.
Post #1610201
Posted Wednesday, September 3, 2014 9:41 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 3,027, Visits: 2,631
Have a look at "Time Intelligence" in Book online. It is a wizard that you can use (in BIDS/SSDT) to generate the MDX code for various time based calculations.


Post #1610342
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse