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 MoazRub · Accepted Answer · 2020-03-06T20:01:48

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

SSAS MDX Previous Year - Country aggregation problem

1 Answers