mdx date filer

samuel rojas 1 Reputation point
2021-07-14T14:38:47.283+00:00

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.
114712-image.png

and get this one when i don't use any filter
114662-image.png

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

Azure Analysis Services
Azure Analysis Services
An Azure service that provides an enterprise-grade analytics engine.
456 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,288 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,871 Reputation points
    2021-07-15T07:23:09+00:00

    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]  
    

    114944-1.png

    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]  
    

    114962-2.png

    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.

    0 comments No comments

  2. samuel rojas 1 Reputation point
    2021-07-15T22:38:28.95+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.