Opening & Closing Balance

Hello Everyone !!!!

Here is a simple method on how to calculate the opening and closing balances in SSAS cube using MDX script. 

I checked lot of blogs and articles myself but I figured that the easiest way could be as below :

Okk !!! So lets start....

1. To find the opening and closing balance we need to have a date/time dimension and a date field in fact table on which the calculations will be based.
2. So I have a Fact table and dimension table as shown in figure below.
3. The important fields here are Item No. along with other columns like Product Group, Item Category etc and the measure fields like Value Quantity, Cost Amount (Actual) etc.
4. Here I ll show you how to calculate opening and closing balance for the Cost Amount (Actual).
5. The dimension table and fact table are related with Posting Date in Fact table and PK Date in dimension table.
6. Now go to Calculations tab in cube design.
7. Create a new calculated member say "Opening_Balance"
8. In the expression enter the following :
1. AGGREGATE(LASTPERIODS(100000,[Time].[PK Date].PrevMember),[Measures].[Cost Amount  Actual])
2. LASTPERIODS command returns the date values for 100000 days from the current date.
3. For example if the expression is LASTPERIODS(5, JANUARY 20) then it will return dates from January 16th to January 20,
4. AGGREGATE command aggregates the Cost Amount (Actual) for all the days returned from LASTPERIODS command.
5. Since this is opening balance I included "PrevMember" so the calculation starts from the previous day.
9. Similarly for "Closing_Balance" the expression would be as below:
1. AGGREGATE(LASTPERIODS(100000,[Time].[PK Date]),[Measures].[Cost Amount  Actual])
2. This is self explanatory. Only difference is that I avoided PrevMember here.