mdx date filer

Question

Hello ,

I have a problem with my mdx query, what i retrieve information using a calculation that allow compare the last and the current value but when i use a filter i don't get the correct result.

I hope to get this result usign an external filter.

and get this one when i don't use any filter

what i want to do is have this measures in just one. here is the query i am using.
Accumulate_Balance_MONTH

([Standard Balance Date].[H_YearMonth].CurrentMember, [Measures].[Accumulate_Balance])

-
(ParallelPeriod(
[Standard Balance Date].[H_YearMonth].CurrentMember.Level,
1,
[Standard Balance Date].[H_YearMonth].CurrentMember
),
[Measures].[Accumulate_Balance])

Accumulate_Balance_YEAR

([Standard Balance Date].[H_YearMonth].CurrentMember, [Measures].[Accumulate_Balance])

(Ancestor(ParallelPeriod(
[Standard Balance Date].[H_YearMonth].[Year],
1,
[Standard Balance Date].[H_YearMonth].CurrentMember
),[Standard Balance Date].[H_YearMonth].[Month]),
[Measures].[Accumulate_Balance])

Answer 1

Hi @samuel rojas ,

Welcome to Microsoft Q&A!

Based on the definition and logic of Accumulate_Balance_MONTH, Accumulate_Balance_YEAR, it seems SSAS returns the correct results.

Please check the members returned for Measures.x, Measures.y and Measures.z testing in sample cube adventure works:

1. With an external filter MDX at month level:

With Member Measures.x AS  
MemberToStr(  
  ParallelPeriod ([Date].[Calendar].[Calendar Year], 1 , [Date].[Calendar].currentmember)   
              )  
Member Measures.y AS  
MemberToStr(  
Ancestor(  
  ParallelPeriod ([Date].[Calendar].[Calendar Year] , 1 , [Date].[Calendar].currentmember)   
   , [Date].[Calendar].[Month])  
              )  
Member Measures.z AS  
MemberToStr  
(ParallelPeriod([Date].[Calendar].CurrentMember.Level,1,[Date].[Calendar].CurrentMember)  
)  
select {Measures.x,Measures.y,Measures.z} on 0,  
[Date].[Calendar].[Month] on 1  
From [Adventure Works]  

2. Don't use any filter MDX:

With Member Measures.x AS  
    MemberToStr(  
      ParallelPeriod ([Date].[Calendar].[Calendar Year], 1 , [Date].[Calendar].currentmember)   
                  )  
    Member Measures.y AS  
    MemberToStr(  
    Ancestor(  
      ParallelPeriod ([Date].[Calendar].[Calendar Year] , 1 , [Date].[Calendar].currentmember)   
       , [Date].[Calendar].[Month])  
                  )  
    Member Measures.z AS  
    MemberToStr  
    (ParallelPeriod([Date].[Calendar].CurrentMember.Level,1,[Date].[Calendar].CurrentMember)  
    )  
    select {Measures.x,Measures.y,Measures.z} on 0,  
    [Date].[Calendar Year].[Calendar Year] on 1  
    From [Adventure Works]  

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

hello,
thanks for your answer, however what i need to do is get the same result Accumulate_Balance_MONTH and Accumulate_Balance_YEAR using just one field.
hope you can help me.