CODEWITHSUNDEEP
ssasmdxmoving-average
Thracian
Thracian

Reputation: 661

MDX query producing #Error as output

In my SSAS Cube, I have a measure called [Sales Total]. What I want to do is to create another measure that would give me the lowest sales figure in the last 6 months. I want this to be a moving minimum, calculated as the min of sales of every time period from the present month to 6 months back.

I wrote my MDX statement but it produces an error and I have hard time trying to figure out why. It is something to do with aggregating Date dimension members into a filter aggregate.

When i choose a single month member from the Date hierarchy, it returns the correct value. When I select multiple members from the hierarchy, as seen below, it errors out.

Any kind of help is appreciated.

WITH 
MEMBER [Measures].[Min Sales Total Rolling 6 months] as '(MIN([Date].[Fiscal Month Hierarchy].currentmember.lag(6):[Date].[Fiscal Month Hierarchy].currentmember,[Measures].[Sales Total]))'
MEMBER [Date].[Fiscal Month Hierarchy].[FilterAggregate] as 
'AGGREGATE({
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201310],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201311],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20141].&[201312],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201401],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201402],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20142].&[201403],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201404],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201405],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20143].&[201406],
[Date].[Fiscal Month Hierarchy].[Quarter].&[20144].&[201407]
})'
SELECT {
[Measures].[Min Sales Total Rolling 6 months]} ON AXIS(0)
FROM [My Cube]
WHERE ([Date].[Fiscal Month Hierarchy].[FilterAggregate])

Upvotes: 1

Views: 1074

Answers (1)

FrankPl
FrankPl

Reputation: 13315

As explained in this blog by one of the SSAS developers already many years ago, multi select does not work with CurrentMember. You should use

MEMBER [Measures].[Min Sales Total Rolling 6 months] as 
MIN(Tail(EXISTING [Date].[Fiscal Month Hierarchy].[Fiscal Month Hierarchy].Members).Item(0).Item(0).lag(6)
    :
    Tail(EXISTING [Date].[Fiscal Month Hierarchy].[Fiscal Month Hierarchy].Members).Item(0).Item(0),
    [Measures].[Sales Total])

instead.

EXISTING gets the set of all selected members. This is needed as there is no single CurrentMember. Then Tail gets the set consisting of the last of these members, Item(0).Item(0) converts that single element set to a member.

Upvotes: 3

Related Questions