September 16, 2010 at 3:10 pm
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...
September 16, 2010 at 11:46 pm
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
Raunak J
September 17, 2010 at 9:34 am
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.
September 17, 2010 at 11:01 pm
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
Raunak J
September 18, 2010 at 1:01 am
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
Raunak J
September 3, 2014 at 11:49 am
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.
September 3, 2014 at 9:41 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply