Search code examples
sql-serverssasmdx

Aggregate function cannot be used in measure dimension

How can I use aggregate function in measure dimension?

I would like to create running total over working days. In these days I have only sales, so I am creating flag:

MEMBER [Measures].[Days] as (
    COUNT(
        [Measures].[New], EXCLUDEEMPTY
    )
)

to indicate it. Now I need to aggregate it, but this code throw me error:

MEMBER [Measures].[Running] as (
    AGGREGATE(
        NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), 
        [Measures].[Days]
    )
)

I want to achieve something like:

Mon 1
Tue 2
Wed 3
Thu 4
Fri 5 
Sat 5 <- it could be also 0, NULL, doesn't matter
Sun 5 <- it could be also 0, NULL, doesn't matter
Mon 6
Tue 7
...
Solution

  • You simply cannot use the Aggregate function on calculated measures. I'd use the following MDX:

    With
Member [Measures].[Days] as
Sum(
    Null:[Date].[Date].CurrentMember,
    IIF(
        [Measures].[New],
        1,
        Null
    )
)