January 19, 2009 at 9:20 am
I have a fact table with sales amounts for the current year and previous year. I have a time dimension with date, month, quarter and year attributes.
I want to create a MDX calculation in SSAS that gives me the % difference between sales for the current year and previous year for each time period.
I've read about the ParallelPeriod function, but I need some help with the MDX script.
Thanks.
January 20, 2009 at 4:53 am
If you take a look at the AdventureWorks cube with Reseller Sales Amount this is what you would do to get the previous years sales for each period and get the growth amount:
WITH
MEMBER measures.[growth in sales] AS
IIF
(
(ParallelPeriod
([Date].[Fiscal].[Fiscal Year]
,1
,[Date].[Fiscal].CurrentMember)
,measures.[reseller sales amount])
= 0
,null
,
(measures.[reseller sales amount]
-
(ParallelPeriod
([Date].[Fiscal].[Fiscal Year]
,1
,[Date].[Fiscal].CurrentMember)
,measures.[reseller sales amount]))
/
(ParallelPeriod
([Date].[Fiscal].[Fiscal Year]
,1
,[Date].[Fiscal].CurrentMember)
,measures.[reseller sales amount]))
,Format_String = "Percent"
SELECT
{
measures.[reseller sales amount]
,measures.[growth in sales]
} ON 0
,NON EMPTY
[Date].[Fiscal].MEMBERS ON 1
FROM [adventure works];
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 20, 2009 at 8:35 am
Thanks, Dan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply