MDX Queries - Parallel Period
Problem:
I had a requirement to compare current year Profit, Daily Turnover, and other measures with Previous year measures
Solution:
I have used Parallel Period function to compare my current year stats with Previous year and MDX statement, finally I produced was as below:
/* Final Query to calculate Requested
Attributes*/
WITH MEMBER
[Measures].[Previous Year Daily Turnover] AS
(ParallelPeriod([Calendar].[Year
Num],1),
[Measures].[Total Daily Turnover]), FORMAT_STRING = 'Currency'
MEMBER [Measures].[Previous Year Profit] AS
(ParallelPeriod([Calendar].[Year
Num],1),
[Measures].[Profit]), FORMAT_STRING
= 'Currency'
MEMBER [Measures].[Previous Year Avg Daily Turnover] AS
(ParallelPeriod([Calendar].[Year
Num],1),
[Measures].[Avg Daily Turnover]), FORMAT_STRING = 'Currency'
MEMBER [Measures].[Previous Year Avg Daily Revenue] AS
(ParallelPeriod([Calendar].[Year
Num],1),
[Measures].[Avg Daily Revenue]), FORMAT_STRING = 'Currency'
MEMBER [Measures].[Previous Year Occupancy %] AS
(ParallelPeriod([Calendar].[Year
Num],1),
[Measures].[Occupancy %]), FORMAT_STRING
= 'Percent'
SELECT
{ [Measures].[Profit], [Measures].[Total Daily
Turnover],[Measures].[Avg Daily Turnover],
[Measures].[Avg Daily Revenue], [Measures].[Previous Year Daily
Turnover],[Measures].[Previous Year Profit]
,[Measures].[Previous Year Avg Daily Turnover],
[Measures].[Previous Year Avg Daily Revenue]
,[Measures].[Occupancy %],[Measures].[Previous Year
Occupancy %] } on
columns,
non empty [Calendar].[Mth
Label].Members on
rows
FROM [Cube_Name]
Comments
Post a Comment