The scenario is this:
Opening Balance =
- my previous closing balance
Closing Balance =
- Opening Balance
- plus new widgets received today
- less widgets completed today
Note how I said that my opening balance equals my previous closing balance? In practical terms, that means:
- count of all new widgets received up to today
- less the count of all widgets completed up to today
In my case a widget refers to a "request", aka an application from a customer for a new credit card. I want to measure how many requests were worked on by my staff during a period of time. That "period of time" gets passed in from a report parameter or query and should always use the 'ReceivedDate' as it's point of reference because we don't want our Opening Balances changing over time (duh!).
My fact table contains a Received Date, a Completed Date and an ItemCount column (there are more but that's all I need for my calculated members). We have an ItemCount column because one request can be for multiple cards. If there's only one card in the request, it contains "1".
There are a multitude of ways to achieve this and here is just one...
First I want to count how many requests were completed in the period. I know what my Received Date member is because the report gives it to me... but how do I know which member of the Completed Date dimension to use? Obviously there is only one physical Date dimension but my cube refers to it multiple times - two of which are Received Date and Completed Date.
I have a problem in that my completed date and my received date are in two different dimensions in my cube so I can't just use any of the MDX relative functions such as ancestor/cousin, sibling, parallelperiod because they aren't relatives at all! What they do have in common though is they share the same member key.
I know the member key from the Received Date so I can use that to find my Completed Date member. But wait, I have multiple levels in my hierarchy; so I need to consider that too.
CREATE MEMBER CURRENTCUBE.[MEASURES].CompletedInPeriod AS
(STRTOMEMBER("[CompletedDate].[Fiscal-Year-Month-Day].[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Level.Name + "].&[" + [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Member_Key + "]")
,Measures.ItemCount),
VISIBLE = 1 ;
The above code shows a simple way to get the answer. Manufacture a string to get the completed date based on the received date and then get the item count for that completed date. It may not be elegant, but it works!
Now I want to get my opening balance. Easy.
CREATE MEMBER CURRENTCUBE.[MEASURES].OpeningBalance AS
IIF([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.FirstSibling IS [ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.Parent, Measures.OpeningBalance)
,([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember.PrevMember, Measures.ClosingBalance)
),
VISIBLE=1;
What this says is, if the current member is equal to the first member (at this level) then get the Opening Balance of my parent. Otherwise get the closing balance of the previous member. In practice that means, if I ask for June 1st, 2010 (the first day of the fiscal year) get the Opening Balance for June 2010. If I ask for June 2010 (the first month of the fiscal year) get the Opening Balance for 2010. If I ask for June 12th, 2010 then get me the Closing Balance for June 11th. If I ask for July 2010, then get me the Closing Balance for June 2010.
And yes, it's recursive ie it's constantly looping back on itself. Don't be scared. SSAS is pretty ok with it so you should be too!
Notice how the last portion refers to the Closing Balance of the previous member? Well I guess that means we need a Closing Balance calculation too! This ones' easy! Remember that the calculation is opening balance + new - completed. And if you think about that a bit you'll see that the opening balance is equal to the previous closing balance. So...
([ReceivedDate].[Fiscal-Year-Month-Day].PrevMember,Measures.ClosingBalance)
+ ([ReceivedDate].[Fiscal-Year-Month-Day].CurrentMember,Measures.ItemCount)
- Measures.CompletedInPeriod,
VISIBLE = 1 ;
It says, that the Closing Balance equals the Closing Balance of the previous member plus the ItemCount for this period less the ItemCount completed during this period (remember our first calculated member was "CompletedInPeriod". For example, If I ask for the closing balance for June 12th 2010, it will get the Closing Balance for June 11th 2010 ie my opening balance, then add the ItemCount for June 12 and minus the ItemCount completed on June 12.
And that's it. Process the cube and away you go.
Next up, I'm going to use this same calculated measure on different hierarchies in the date dimension. For example, I have a Fiscal-Year-Month-Day, a Fiscal-Year-Month, a Calendar-Year-Month-Day and a Calendar-Year-Month... and many more when you look at Quarters, weeks and semesters! But that's for another day...
Posts
