SSAS: MDX Calculated Measures that Require Date Comparison
Often there is requirement to calculate measures based on two different date dimensions. For e.g. how many orders that were delivered this month was actually ordered last month? How many orders were delivered in the same month as they were ordered? How many orders were carried forward to next month?
Using MDX set functions EXISTS(),FILTER() and Range operator these calculations can be easily achieved provided the Date Dimension conform to same key columns.
Backlog Orders
The following MDX will fetch all the orders delivered this month that were actually ordered in previous months (backlog)
//ORDERS FROM PREV MONTHS DELIVERED THIS MONTH
WITH MEMBER [Measures].[Backlog Orders] AS
SUM(FILTER(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
([Date].[Month Name].Properties("Key0") + [Date].[Month Name].Properties("Key1"))<
([Delivery Date].[Month Name].CurrentMember.Properties("Key0") + [Delivery Date].[Month Name].CurrentMember.Properties("Key1"))),
[Measures].[Internet Transaction Count])
SELECT {[Measures].[Internet Transaction Count],[Measures].[Backlog Orders] } ON COLUMNS,
Descendants([Delivery Date].[Calendar].[Calendar Year].&[2007],[Delivery Date].[Calendar].[Month]) ON ROWS
FROM [Adventure Works]
- The EXISTS() part returns the set of all order dates for the delivery date in question.
- The FILTER() part excludes any order date that equals the delivery date and excludes any orders after the delivery date by comparing the Key values of order and delivery dates. Notice that since AdventureWorks date dim uses a composite Key for Month, Keyn has to be used. If your Month Key value is in YYYYMM format, then you could just use
([Date].[YYYYMM].Properties("Key") < ([Delivery Date].[YYYYMM].CurrentMember.Properties("Key")
The result of this MDX query will be as shown in Screen Capture 1
Screen Capture 1- Backlog Orders
Current Orders
The same MDX used for Backlog orders can be used to get all new orders delivered in the same month as they were ordered (Current Orders) just by changing the FILTER() condition
//ORDERS DELIVERED SAME MONTH
WITH MEMBER [Measures].[Current Orders] AS
SUM(FILTER(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
([Date].[Month Name].Properties("Key0") + [Date].[Month Name].Properties("Key1")) =
([Delivery Date].[Month Name].CurrentMember.Properties("Key0") + [Delivery Date].[Month Name].CurrentMember.Properties("Key1"))),
[Measures].[Internet Transaction Count])
SELECT {[Measures].[Internet Transaction Count],[Measures].[Current Orders] } ON COLUMNS,
Descendants([Delivery Date].[Calendar].[Calendar Year].&[2007],[Delivery Date].[Calendar].[Month]) ON ROWS
FROM [Adventure Works]
Notice that the FILTER() condition excludes any orders outside the delivery dates.
The result of this MDX query will be as shown in Screen Capture 2
Screen Capture 2 – Current Orders
Orders Carried Forward
The following MDX calculates how many orders made in the current period were actually delivered in the subsequent periods (Carried Forward).
//ORDERS CARRIED FORWARD
WITH MEMBER [Measures].[Orders Carried Forward] AS
SUM(CrossJoin(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
[Delivery Date].[Calendar].CurrentMember.NextMember:NULL),[Measures].[Internet Transaction Count] )
SELECT {[Measures].[Internet Transaction Count],[Measures].[Orders Carried Forward] } ON COLUMNS,
Descendants([Delivery Date].[Calendar].[Calendar Year].&[2007],[Delivery Date].[Calendar].[Month]) ON ROWS
FROM [Adventure Works]
- The EXISTS() part returns the set of all order made in the current period.
- The Range operator(:) returns the set of all delivery dates after the current period.
- The CROSSJOIN() produces the intersection set of all order dates that were delivered in subsequent periods
The result of this MDX query will be as shown in Screen Capture 3
Screen Capture 3 – Orders Carried Forward
The Lot
The MDX combo for the above would be
//THE LOT
WITH MEMBER [Measures].[Backlog Orders] //ORDERS FROM PREV MONTHS DELIVERED THIS MONTH
AS SUM(FILTER(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
([Date].[Month Name].Properties("Key0") + [Date].[Month Name].Properties("Key1")) <
([Delivery Date].[Month Name].CurrentMember.Properties("Key0") + [Delivery Date].[Month Name].CurrentMember.Properties("Key1"))),
[Measures].[Internet Transaction Count])
MEMBER [Measures].[Current Orders] //ORDERS DELIVERED SAME MONTH
AS SUM(FILTER(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
([Date].[Month Name].Properties("Key0") + [Date].[Month Name].Properties("Key1")) =
([Delivery Date].[Month Name].CurrentMember.Properties("Key0") + [Delivery Date].[Month Name].CurrentMember.Properties("Key1"))),
[Measures].[Internet Transaction Count])
MEMBER [Measures].[Orders Carried Forward] //ORDERS CARRIED FORWARD
AS SUM(CrossJoin(EXISTS([Date].[Month Name].Children,[Delivery Date].[Month Name].CurrentMember,"Internet Orders"),
[Delivery Date].[Calendar].CurrentMember.NextMember:NULL),[Measures].[Internet Transaction Count] )
SELECT {[Measures].[Internet Transaction Count],[Measures].[Backlog Orders],
[Measures].[Current Orders],[Measures].[Orders Carried Forward] } ON COLUMNS,
Descendants([Delivery Date].[Calendar].[Calendar Year].&[2007],[Delivery Date].[Calendar].[Month]) ON ROWS
FROM [Adventure Works]
Notice that from Screen Capture 4,
- Backlog orders + Current Orders = Delivery Count
- Orders Carried Forward from the current period becomes the Backlog Orders for the next period
Screen Capture 4 – Backlog Orders, Current Orders and Orders Carried Forward
Benny Austin
SSAS: MDX Calculated Measures that Require Date Comparison wp.me/pxNuz-pW via @BennyAustin
—
Louise Smit (@LouiseSmit6) June 05, 2012
