none
ParallelPeriod calculation for a measure for last year Quarter/Month RRS feed

  • Question

  • Hi,

    I have a task to calculate Total for a measure for last year. I have the calculation working for regular Time/Time hierarchy but not for another Time Hierarchy with Current Year/Quarter/Month flags. The reason for adding that Hierarchy to Time dimension was to be able to use the Current Flag filter from PowerBI report with Live Connect. The Date attribute would be updated daily with flag value = 'Y' in the dsv yielding a dynamic Current and Previous year totals in the PowerBI report.

    This is the Time Hierarchy for me in this cube.

    Previous Year Total calculation which is working with Time.Time hierarchy - 

        SCOPE([Time].[Time].[Year].MEMBERS);                                    [Measures].[PY Book ResaleSALE]=([Measures].[Book Resale - SALE], [Time].[Current].&[],     PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember));

        END SCOPE;    

        SCOPE([Time].[Time].[Quarter].MEMBERS);                                    [Measures].[PY Book ResaleSALE]=([Measures].[Book Resale - SALE], [Time].[Current].&[],      PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember));

        END SCOPE;

    But not working with Time.Current Hierarchy as - 

        SCOPE([Time].[Current].[Curr Quarter Flag].MEMBERS);                                    [Measures].[PY Book ResaleSALE]=([Measures].[Book Resale - SALE], [Time].[Current].&[],     PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember));

        END SCOPE;    

        SCOPE([Time].[Current].[Curr Month Flag].MEMBERS);                                    [Measures].[PY Book ResaleSALE]=([Measures].[Book Resale - SALE], [Time].[Current].&[],    PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CurrentMember));

    END SCOPE;

    Is there any problem with this calculation, MDX itself or the SCOPE?

    Any help is appreciated!!

    Wednesday, November 14, 2018 4:15 AM

All replies

  • Hi sagaranup,

    You would need to add members of Time.Time hierachy in your Scope define, or using Root function if you would not like to specify hierarchy when using parallelpeiod function, the expression looks like:

    SCOPE (ROOT(Time.Hierarchy1), ROOT(Time.Hierarchy2),ROOT(Time.Hierarchy3));
      this = (ParallelPeriod(Time.Hierarchy4.CurrentMember),Measures.[Calc Marge]);
    END SCOPE;

    See explain here:

    [MDX] Do not specify hierarchy when using parallelperiod function on time dimension

    Regards,

    Pirlo Zhang 


    MSDN Community Support<br/> Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Wednesday, November 14, 2018 9:17 AM