PY Total for Selected Time Attribute Hierarchy in SSAS cube

Anup Pudasaini 46 Reputation points
2021-10-26T14:08:58.03+00:00

I was able to calculate PY Total for Selected Time Dimension's Attributes in individual Hierarchy but cannot figure out how to make it work for both Time Dimension Hierarchies with single calculated measure.

I had to USE Dynamic SET to populate Days and use AGGREGATE on the SET as follows -

CREATE DYNAMIC SET CURRENTCUBE.[Days]                                                          
 AS {EXISTING [Time].[Time].[Day]};                   
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                       
 AS AGGREGATE(                                                                  
        GENERATE(EXISTING [Days],       
            {PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember)}  
        ),  
[Measures].[Sales]  
),     
FORMAT_STRING = "#,#",   
VISIBLE = 1  
  
----  
CREATE DYNAMIC SET CURRENTCUBE.[Days]   
 AS {EXISTING [Time].[Current].[Curr Day Flag]};                  
CREATE MEMBER CURRENTCUBE.[Measures].[PY Sales Selected]                                       
 AS AGGREGATE(                                                                  
        GENERATE(EXISTING [Days],       
            {PARALLELPERIOD([Time].[Current].[Curr Year Flag],1,[Time].[Current].CurrentMember)}  
        ),  
[Measures].[Sales]  
),     
FORMAT_STRING = "#,#",   
VISIBLE = 1  

Both calculations work individually but I would like to combine them so that I could just have one Calculated Measure [Measures].[PY Sales Selected].

We have following Time dimension structure -

Time Dimension Structure
143848-time-dimension.png

Attribute Relationship
143863-attribute-relationship.png

I would appreciate any help!!

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,239 questions
{count} vote

Accepted answer
  1. Alexei Stoyanovsky 3,411 Reputation points
    2021-10-27T08:30:17.7+00:00

    Use a set of TimeIDs, add TimeID to the Time hierarchy (hiding as necessary), and do ParallelPeriod over the Time hierarchy. You don't need Existing in dynamic set definition, you add it later when defining calculated members.
    PS that was one beautifully prepared question!

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-10-27T06:13:22.73+00:00

    Hi @Anup Pudasaini ,

    Welcome to Microsoft Q&A!

    Different hierarchies don't add up in columns, they cross join and form multiple columns. In order to combine members from different hierarchies into the same column, we need to resolve the dimensionality issue first. Choosing a "host" hierarchy and forcing all the members into the same dimensionality is our first task. But there have some limitations with user hierarchies, and when more than one member should be "hosted" in the other hierarchy, all of them should be defined as calculated members, one by one. This can be an administrative burden and so it is advised that this solution is used only in cases with few members to be projected on the other hierarchy. Naturally, the hierarchy with more elements to be shown in the report should be the "hosting" one. For more information, please refer to the book: MDX with SSAS 2012 Cookbook.

    Best regards,
    Carrin


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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