MDX Queries Part 2: ClosingPeriod function
Well, I have got something to work now but it’s still not dynamic enough for my liking, using the ClosingPeriod MDX function
[sql]WITH
MEMBER [Measures].[Current Balance]
AS
([Measures].[EOD Book Balance],
ClosingPeriod([Time].[Calendar].[Date],
[Time].[Calendar].[Month].&[2]&[2007]))
MEMBER [Measures].[Opening Balance 2007]
AS
([Measures].[EOD Book Balance],
ClosingPeriod([Time].[Calendar].[Date],
[Time].[Calendar].[Month].&[12]&[2006]))
MEMBER [Measures].[£ Change]
AS
([Time].[Simple Date].CurrentMember ,[Measures].[Current Balance])
-([Time].[Simple Date].CurrentMember,[Measures].[Opening Balance 2007])
SELECT
{[Measures].[Current Balance] ,
[Measures].[Opening Balance 2007],
[Measures].[£ Change] } ON COLUMNS,
{nonempty([Products].[Product ID].[Product ID])} ON ROWS
FROM [DailyBalances][/sql]
