Am using the MDX query in the SSAS server.
I want to get the current year(01/01/2002 - 31/12/2002) and previous year(01/01/2001 - 31/12/2001) data for a specific date range(dynamically change) in a single MDX query based on the used measure and dimension. Here [Measures].[Sales Amount Quota] is measure field and [Employee].[Title] is dimension field.
The result must contain 1 dimension column, 1 measure column for the current year, and another 1 measure column for the previous year.
I have prepared one query but it is not worked, please find the query below:
WITH
MEMBER [Measures].[current year data] AS
CASE WHEN CDate([Hire Date].CurrentMember.MemberValue) >= CDate('2002-01-01') AND CDate([Hire Date].CurrentMember.MemberValue) <= CDate('2002-12-31') THEN [Measures].[Sales Amount Quota] ELSE null END
MEMBER [Measures].[previous year data] AS CASE WHEN CDate([Hire Date].CurrentMember.MemberValue) >= CDate('2001-01-01') AND CDate([Hire Date].CurrentMember.MemberValue) <= CDate('2001-12-31') THEN [Measures].[Sales Amount Quota] ELSE null END
SELECT{
[Measures].[current year data],[previous year data]} ON COLUMNS ,NONEMPTY(((Order((([Employee].[Title].[Title].ALLMEMBERS)),[Employee].[Title].CurrentMember.Member_Caption,ASC))),
{[Measures].[current year data],[previous year data]}) ON ROWS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
But when am using the date field in the order clause, the requirement is partially achieved.
WITH
MEMBER [Measures].[current year data] AS
CASE WHEN CDate([Hire Date].CurrentMember.MemberValue) >= CDate('2002-01-01') AND CDate([Hire Date].CurrentMember.MemberValue) <= CDate('2002-12-31') THEN [Measures].[Sales Amount Quota] ELSE null END
MEMBER [Measures].[previous year data] AS
CASE WHEN CDate([Hire Date].CurrentMember.MemberValue) >= CDate('2001-01-01') AND CDate([Hire Date].CurrentMember.MemberValue) <= CDate('2001-12-31') THEN [Measures].[Sales Amount Quota] ELSE null END
SELECT{
[Measures].[current year data],[Measures].[previous year data]} ON COLUMNS ,NONEMPTY(((Order((([Employee].[Title].[Title].ALLMEMBERS)),[Employee].[Title].CurrentMember.Member_Caption,ASC))*
(Order((([Employee].[Hire Date].[Hire Date].ALLMEMBERS)),[Employee].[Hire Date].CurrentMember.Member_Caption,ASC))),
{[Measures].[current year data],[Measures].[previous year data]}) ON ROWS FROM [Sales Targets] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
But the exact result I want is
In the result, dimension values must be grouped and measure values also are aggregated
The main objective needs to compare the data using period over period logic for a dynamic date range in the MDX query
Can anyone please help me with this?
