MDX YTD calculated measure

Question

I'm still new to MDX and I'm trying to get some basic functions to work in my SSAS cube. Can you guys point out what I'm doing wrong here? I've added a calculated measure to my cube with the following code:

CREATE MEMBER CURRENTCUBE.[Measures].[Amount YTD]
AS
  AGGREGATE(
    YTD([OrderDate].[Calendar].CurrentMember)
   ,[Measures].[Amount]),
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'MyMeasureGroup';

After that I'm trying to get some data going...

SELECT 
NON EMPTY
{
  [Measures].[Amount]
, [Measures].[Amount YTD]
} ON COLUMNS,
NON EMPTY
{
  [OrderDate].[Month].ALLMEMBERS *
  [Product].[Product Group].ALLMEMBERS 
} ON ROWS
FROM (SELECT ([OrderDate].[Year].&[2014-01-01T00:00:00]:
              [OrderDate].[Year].&[2015-01-01T00:00:00]) ON COLUMNS
FROM [SalesOrderIntake])

This is the output I'm getting:

I'm not seeing any errors in my Output messages, which makes it difficult for me to figure out what is acting up. Hoping you guys can help me out on this one.

FYI: the actual select is just for testing purposes. I just want to get that YTD running. I've tried several things and it always comes out empty, so I was hoping to get some actual errors if I would query it directly in SSMS instead of using a BI tool. Also, the OrderDate dimension is a generated Time dimension which was provided to me by VS.

what does the error say? .. double click on the cell in ssms and it will tell you the error message — whytheq, Jun 09 '16 at 13:49

score 4 · Accepted Answer · answered Jun 09 '16 at 13:53

4

In your query you're using what looks like an attribute hierarchy:

[OrderDate].[Month].ALLMEMBERS

Whereas the measure uses the user hierarchy:

[OrderDate].[Calendar]

If you use Calendar in your script does it work ok?

answered Jun 09 '16 at 13:53

whytheq

34,466
65
172
267

The error I was getting was something about not being able to find Calendar. I've changed my calculated member to SUM(YTD([OrderDate].[Year].CurrentMember),[Measures].[Amount]), and that seems to work! Your tips guided me there, thanks! – Jens Jun 09 '16 at 14:08
@whytheq - Do you really think the "error" is because no member from "Calendar" hierarchy was in scope? Because by default the "All" member is in scope. So while this would not return any result, would not lead to error also. – SouravA Jun 09 '16 at 14:11
Turns out I had to use SUM(YTD([OrderDate].[Year - Quarter - Month - Date].CurrentMember),[Measures].[Amount]) for it to also give *correct* results. I guess for some reason the "Calendar" thing was unknown in my Dimension. Thought it was a generic name since I found it all over the interwebs... – Jens Jun 09 '16 at 14:17

SouravA · Answer 2 · 2016-06-09T14:08:23.433

#Error usually crops up when there are run time errors in MDX code. I could think of one scenario where the error might crop up. You are using [OrderDate].[Calendar].CurrentMember in the calculated member. But if instead of one, there are multiple members from this hierarchy in scope, it will throw an error.

The below is a scenario from Adventure Works.

with member abc as 
sum(YTD([Date].[Calendar].currentmember), [Measures].[Internet Sales Amount])

select abc on 0
from [Adventure Works]
where {[Date].[Calendar].[Date].&[20060115], [Date].[Calendar].[Date].&[20060315]}

P.S. Thanks to @whytheq for teaching me this trick of checking this error by double clicking the cell :) Cheers.

score 0 · Answer 3 · edited Dec 10 '20 at 21:23

0

I know, its an old post, but in the interest of posterity..

The correct approach is :

Aggregate
(
    PeriodsToDate
    (
        [OrderDate].[Calendar].[Fiscal Year]
        ,[OrderDate].[Calendar].CurrentMember
    )
    ,[Measures].[Amount]
)

edited Dec 10 '20 at 21:23

aalbagarcia

1,019
7
20

answered Dec 09 '20 at 19:49

Biswajit Panda

1

MDX YTD calculated measure

3 Answers3