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

Popular posts from this blog

Calculating Age of the person in T-SQL

How to Troubleshoot Subscription issue in Reporting Services