MDX-Query to show year level data for all years except last one, and month level data for last year
Q: I need an MDX query to show year level data for all years except the last one, and month level data for the last year.
A: Example of query
SELECT {NULL:
ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember).PrevMember
, DESCENDANTS(ClosingPeriod([Date].[Calendar].[Calendar Year], [Date].[Calendar].DefaultMember)
, [Date].[Calendar].[Month])
}
ON 0
FROM [Sales Summary]
Results:
CY 2001 CY 2002 CY 2003 January 2004 February ... July 2004 August 2004
$11,331,808.96 $30,674,773.18 $41,993,729.72 $3,002,792.27 ... $50,840.63 (null)
Tags: mdx, faq, time dimension