MDX running total with cross join

Question

MDX newbie question

I am using the FoodMart example database to express my problem.

I need to show running total of the "Unit Sales" measure on the column with "Month", "Year", and "Product Name" dimensions on the rows:

Year    Month     Product   |  Unit Sales    UnitSoldIncludngThisProdTillDate
                            |
2014    Jan        P1       |    4                4
                   P2       |    2                6
                   P3       |    0                6
        Feb        P1       |    1                7
                   P2       |    0                7
                   P3       |    3               10
2015    Jan        P1       |    7               17

...... and so on

Without the cross join with Product, the query runs fine. However, joining with Product does not give what I want.

How to solve this problem?

The MDX query that I am running is

AGGREGATE(YTD(), [Measures].[Unit Sales])
SELECT {[Measures].[Unit Sales], MEASURES.YTDDEMO} ON 0,
NON EMPTY {[Time].[Month].Members * [Product].[ProductLevel].Members} ON 1
FROM  [TestFoodMart]

Added on 10th June 2015

I have been using the correct syntax etc.

Without the Join I get the following:

|                  | Unit Sales | Sales To Date |
+------+-----------+------------+---------------+
| 2013 | April     |     45,049 |        45,049 |
|      | August    |     44,777 |        89,826 |
|      | December  |            |        89,826 |
|      | February  |     44,431 |       134,257 |
|      | January   |     46,313 |       180,570 |
|      | July      |     46,671 |       227,241 |
|      | June      |     45,611 |       272,852 |
|      | March     |     46,334 |       319,186 |
|      | May       |     45,085 |       364,271 |
|      | November  |     53,807 |       418,078 |
|      | October   |     43,945 |       462,023 |
|      | September |     47,964 |       509,987 |

With the Join I get the following:

                                                                | Unit Sales | YTDDEMO |
+------+-----------+---------------------------------------------+------------+---------+
| 2013 | April     | ADJ Rosy Sunglasses                         |         38 |      38 |
|      |           | Akron City Map                              |         29 |      29 |
|      |           | Akron Eyeglass Screwdriver                  |         34 |      34 |
|      |           | American Beef Bologna                       |         28 |      28 |
|      |           | American Chicken Hot Dogs                   |         25 |      25 |
|

As you can see, the aggregation is not working

Answer 1

You're missing the WITH line of code:

WITH MEMBER MEASURES.YTDDEMO AS
  AGGREGATE(YTD(), [Measures].[Unit Sales])
SELECT 
  {[Measures].[Unit Sales], MEASURES.YTDDEMO} ON 0,
  NON EMPTY 
  {[Time].[Month].Members * [Product].[ProductLevel].Members} ON 1
FROM  [TestFoodMart]

Please be aware of the Remarks section in the MSDN definition of the YTD function: https://msdn.microsoft.com/en-us/library/ms146039.aspx

If a member expression is not specified, the default is the current member of the first hierarchy with a level of type Years in the first dimension of type Time in the measure group. The Ytd function is a shortcut function for the PeriodsToDate function where the Type property of the attribute hierarchy on which the level is based is set to Years. That is, Ytd(Member_Expression) is equivalent to PeriodsToDate(Year_Level_Expression,Member_Expression). Note that this function will not work when the Type property is set to FiscalYears.

If the type property of your year attribute hierarchy is not set to time, then the YTD function will not work.

Does this equivalent version work?

WITH 
  MEMBER MEASURES.YTDDEMO AS 
    Aggregate
    (
      PeriodsToDate([Time].[Year]) <<//change to what your year level is
     ,[Measures].[Unit Sales]
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Unit Sales]
     ,MEASURES.YTDDEMO
    } ON 0
 ,NON EMPTY 
    {[Time].[Month].Members * [Product].[ProductLevel].Members} ON 1
FROM [Adventure Works];

Answer 2

Could you try this, I think it's only a syntax problem.

AGGREGATE(YTD(), [Measures].[Unit Sales])
SELECT {[Measures].[Unit Sales], MEASURES.YTDDEMO} ON 0,
NON EMPTY ([Time].[Month].Members * [Product].[ProductLevel].Members) ON 1
FROM  [TestFoodMart]