SSAS MDX Previous Year - Country aggregation problem

Question

my previous question for SSAS MDX Previous Year - Ignore Filter was solved. Now i have an other problem. I get now the right previous year result for the store but the aggregation on country level is wrong.

My problem was i doesn't get the previous year value for a store because the filter kicked that value:

Store | Turnover Actual | Turnover PrevYear

Hamburg | 100.00 | 120.00

Munich | 140.00 | 130.00

Cologne | 90.00 | 110.00

Berlin | 150.00 | null

I tried then this query from MoazRub:

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    SUM(      
       ([Store Status].[Store Status Type].defaultmember, ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       ) 
    )
Select  
non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
on Columns,
non empty{[Store].[Store].[Store].members}
on Rows
from [Sales Cube]
where (
[Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
[Store Status].[Store Status Type].&[Comparable], 
[Store].[Country].[Country].&[GERMANY]
)

I get the right result on the Store-Level:

Store | Turnover Actual | Turnover PrevYear

Hamburg | 100.00 | 120.00

Munich | 140.00 | 130.00

Cologne | 90.00 | 110.00

Berlin | 150.00 | 120.00

On the Country-Level I get a wrong SUM because now a other store "Bremen" which is "Not comparable" at january, 2020 is in the sum included.

TOTAL should be (without Bremen which one is "Not comparable": 460.00

TOTAL "is situation" : 580.00 (includes Bremen)

Query on Country-Level:

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    SUM(      
       ([Store Status].[Store Status Type].defaultmember, ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       ) 
    )
Select  
non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
on Columns,
non empty{[Store].[Country].[Country].&[GERMANY]}
on Rows
from [Sales Cube]
where (
[Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
[Store Status].[Store Status Type].&[Comparable]
)

How can I sum on Country-Level only the comparable stores?

MoazRub · Answer 1 · 2020-03-09T11:13:20.503

My understanding is that berlin Jan 2019 is a special case. You just want to ignore the filter "[Store Status].[Store Status Type].&[Comparable]" for it once. For all the rest you want the filter to work? If that is the case then use the query below. But Make sure you substitute the correct value for

[Date].[Year - Quarter - Month - Date].CurrentMember="January 2020" In place of "January 2020" use the caption of [Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1]

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    case when [Store].[Store].currentmember= "Berlin" and [Date].[Year - Quarter - Month - Date].CurrentMember="January 2020"
    then 
    SUM(      
       ([Store Status].[Store Status Type].defaultmember, ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       ) 
     else 
     SUM(      
       ( ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
       ,   [Measures].[Turnover Actual]
       )
       end 
    )
Select  
non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
on Columns,
non empty{[Store].[Store].[Store].members}
on Rows
from [Sales Cube]
where (
[Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
[Store Status].[Store Status Type].&[Comparable], 
[Store].[Country].[Country].&[GERMANY]
)

based on the excel provided, My understand is it sums up all stores, even those that are not in germany.Based on this understanding, You need to use "Existing" keyword to force evaluation of this expersion in context.Try the query below

 with 
    member [Measures].[Turnover PrevYear] as 
    IIF( IsEmpty([Measures].[Turnover Actual] ), 
        NULL,   
        SUM( existing     
           ([Store Status].[Store Status Type].defaultmember, ParallelPeriod( [Date].[Year - Quarter - Month - Date].[Year],1,[Date].[Year - Quarter - Month - Date].CurrentMember))
           ,   [Measures].[Turnover Actual]
           ) 
        )
    Select  
    non empty{[Measures].[Turnover Actual],[Measures].[Turnover PrevYear]} 
    on Columns,
    non empty{[Store].[Country].[Country].&[GERMANY]}
    on Rows
    from [Sales Cube]
    where (
    [Date].[Year - Quarter - Month - Date].[Month].&[2020]&[1], 
    [Store Status].[Store Status Type].&[Comparable]
    )

Thanks but this is a hard coded solution. Berlin is not a special case. A store can have a "comparable" Status or a "Not comparable" status defined by a algorithm. This algorithm is unimportant at this query. My goal ist to have a query shows me all "Comparable" stores at a choosed Year-Month with actual valued and previous year values not matter it is or not comparable a year ago. This works with your first query from the another question. The problem ist the aggregation on country level. It sums not only the store list. it sums all stores at previous year. — Alexo, Mar 09 '20 at 08:56
So basicly it is adding values of stores that are not in that country. Like for germany, It is summing up stores that are not in germany? Plus I would suggest you attach a sample excel screen shot of what is returned as to what should be returned — MoazRub, Mar 09 '20 at 09:55
Ok. I will send you a excel file in few minutes to your mail adress or can i attach this file to my question? — Alexo, Mar 09 '20 at 10:00
Sure you can share a screen shot here explaining your problem or email me the excel. — MoazRub, Mar 09 '20 at 10:04
Thanks. Unfortunately your last query doesn't work. When i add the "Existing" Keyword the result of the [Measures].[Turnover PrevYear] becomes NULL. Also it becomes NULL on the Store-Level. — Alexo, Mar 09 '20 at 11:36

SSAS MDX Previous Year - Country aggregation problem

1 Answers1