Strange results from MDX Query

I am currently working on adding forecasting capabilities to an existing customer cube. The MDX to do this is based on calculations in an existing spreadsheet model. The spreadsheet works very well in terms of modeling the business accurately, but it’s a manually intensive process and does not recalculate automatically as needed. Perfect candidate for implementation as part of an Analysis Services cube.

This query contains the following calculations:

• Sales_0YP – Sales for the current period
• Sales_1YP – Sales for 1 year prior
• Sales_2YP – Sales for 2 years prior
• Sales_3YP – Sales for 3 years prior
• Sales_4YP – Sales for 4 years prior
• PCComplete – (per cent complete) Within a month, how complete are we for this day. Calculated by dividing MTD sales by total for the month. Last day of month gets us to 100%
• PCofMonth – (per cent of month) Within a month, what does this day represent for the month’s Sales activity. Calculated by dividing Current day’s sales by total for the month.
• MonthProjection – Calculated by dividing MTD by PCComplete. Pretty slick (I did not come up with the technique – it was a very smart business analyst I am working with, so I can say that).
• WhatPeriod – For debugging, I have a calculated member that shows me which period is in scope
• ShouldBe – an empty string to create a place for calcs when I paste this into Excel for testing

The cube already contains an MTD calculation, which hangs off of a utility (calculation) dimension known as [Time Scope].

The script for the database is here, and the query below (with and without SOLVE_ORDER) are zipped here.

Here’s the whole query, morphed to run against Adventure Works:

WITH
MEMBER [Time Scope].[Time Scope].[WhatPeriod] AS (ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Month]).Name)

MEMBER [Sales_0YP] AS ([Measures].[Internet Sales Amount]),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_1YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],1)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_2YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],2)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Sales_3YP] AS ([Internet Sales Amount], ParallelPeriod([Date].[Fiscal].[Fiscal Year],3)),
FORMAT_STRING = "#,##0.00;-#,##0.00", SOLVE_ORDER = 0

MEMBER [Time Scope].[Time Scope].[PCComplete] AS
([Time Scope].[Time Scope].[MTD]) /
([Time Scope].[Time Scope].[Current], ANCESTOR([Date].[Fiscal], [Date].[Fiscal].[Month]))
, FORMAT_STRING = "Percent", SOLVE_ORDER = 2

MEMBER [Time Scope].[Time Scope].[PCofMonth] AS
([Time Scope].[Time Scope].[Current]) /
([Time Scope].[Time Scope].[Current], ANCESTOR([Date].[Fiscal], [Date].[Fiscal].[Month]))
, FORMAT_STRING = "Percent", SOLVE_ORDER = 2

MEMBER [Time Scope].[Time Scope].[MonthProjection] AS
([Time Scope].[Time Scope].[MTD]) /
([Time Scope].[Time Scope].[PCComplete])
, SOLVE_ORDER = 13
, FORMAT_STRING = "#,##0.00;-#,##0.00"
MEMBER [Time Scope].[Time Scope].[ShouldBe] AS ""
SELECT
{[Sales_0YP], [Sales_1YP], [Sales_2YP], [Sales_3YP]} ON 0
,{DESCENDANTS([Date].[Fiscal].[Fiscal Year].&[2005], [Date].[Fiscal].[Date] )}
*
{
[Time Scope].[Time Scope].[Current]
,[Time Scope].[Time Scope].[Previous]
,[Time Scope].[Time Scope].[WhatPeriod]
,[Time Scope].[Time Scope].[MTD]
,[Time Scope].[Time Scope].[PCComplete]
,[Time Scope].[Time Scope].[PCofMonth]
,[Time Scope].[Time Scope].[MonthProjection]
,[Time Scope].[Time Scope].[ShouldBe]
} ON 1
FROM
[Adventure Works]

Here’s the strange result:

• All of the component calcualtions resolve correctly, but the PCComplete always reflects the calculation for the last day of the month, regardless of which day is in scope

If you’re reaching this via my twitter post, feel free to leave a comment (140 chars might be a bit too short for an explanation).