WITH MEMBER [Measures].[Starting Invemtory] AS
'IIF(IsLeaf([Year].CurrentMember),
[Measures].[Opening Inventory],
([Measures].[Opening Inventory], OpeningPeriod ([Year].Levels(0),[Year].CurrentMember)))'
MEMBER [Measures].[Closing Invemtory] AS
'IIF(IsLeaf([Year].CurrentMember),
[Measures].[Ending Inventory],
([Measures].[Ending Inventory], ClosingPeriod ([Year].Levels(0),[Year].CurrentMember)))'
SELECT
CrossJoin (
{ [100-10] },
{ [Measures].[Starting Invemtory], [Measures].[Closing Invemtory]}
)
ON COLUMNS,
Hierarchize ( [Year].Members,POST )
ON ROWS
FROM Sample.Basic
100-10 100-10
Starting Inventory Closing Inventory
Jan 14587 14039
Feb 14039 13566
Mar 13566 13660
Qtr1 14587 13660
Apr 13660 14172
May 14172 15127
Jun 15127 15580
Qtr2 13660 15580
Jul 15580 14819
Aug 14819 14055
Sep 14055 13424
Qtr3 15580 13424
Oct 13424 13323
Nov 13323 13460
Dec 13460 12915
Qtr4 13424 12915
Year 14587 12915
