SSAS MDX Previous Year - Ignore Filter

Question

i try to get actual turnover and the turnover of the previous year in the same period.

I write this query:

with 
member [Measures].[Turnover PrevYear] as 
IIF( IsEmpty([Measures].[Turnover Actual] ), 
    NULL,   
    SUM(      
       (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]
)

Now i get the following result:

Store | Turnover Actual | Turnover PrevYear

Hamburg | 100.00 | 120.00

Munich | 140.00 | 130.00

Cologne | 90.00 | 110.00

Berlin | 150.00 | null

Berlin doesn't have a prevoius year value because the store had the status "Not Comparable" in january 2019. This "Store Status" Filter operate not only for the actual turnover also for the previous year calculation. How can i ignore this filter on the calculation? I wants to get a value for berlin from previous year no matter the store was "Not comparable" in the last year. This filter is only for the actual situation to kick the actual "Not comparable" Stores out of this result list.

score 1 · Accepted Answer · answered Mar 06 '20 at 14:11

1

You need to add "default member" to any calculation that you want to ignore the context. Your code will be

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

answered Mar 06 '20 at 14:11

MoazRub

2,881
2
10
20

Thanks for the answer @MoazRub. I try this version and get now a value for Berlin. This is cool, but I have now a new problem. If i run this query not on Store-Level rather on Country Level i have a wrong sum result for germany. Let's assume Berlin have a previous year result of 100.00. Then my sum for germany should be 460.00 right? My result is 580.00. Explanation: It exists one Store more "Bremen" which one is "Not Comparable" at january, 2020. This store have a previous year value of 120.00 . On store level this store is filtered out. On Country Level it is included. – Alexo Mar 06 '20 at 14:36
@Alexo. You are welcome. I would request you to keep one problem on a post. This will help anyone else facing the same problem get the solution easily. Plus there is a posibility that the solution to the next problem may be diffrent. So it will be relativly hidden. So mark this as answered. Post your next problem, i will be happy to solve that too. – MoazRub Mar 06 '20 at 15:09
Ok. I will post a new question. – Alexo Mar 06 '20 at 15:34

SSAS MDX Previous Year - Ignore Filter

1 Answers1

Linked