CODEWITHSUNDEEP
sql-serverssasmdx
Testtest11
Testtest11

Reputation: 367

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
...

Upvotes: 0

Views: 1094

Answers (1)

Danylo Korostil
Danylo Korostil

Reputation: 1484

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
    )
)

Upvotes: 1

Related Questions