SSAS MDX SCOPE move CurrentMember to CY2019

Refa Abay 1 Reputation point
2021-12-22T17:11:36.373+00:00
-- 602 VVJT (VVJT)
SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[602]);     --gw 16.10.14
THIS= 
    Iif([Zeit].[Hierarchie J-M-T].CurrentMember.Level.Ordinal<>3,Null,
        ([Zeit].[Hierarchie J-M-T ].CurrentMember.lag(728)
       ,[Zeitanalyse].[Zeitanalyse].[Element].&[101])
    );    
END SCOPE;    


-- 701 VVJP (VVJP)
SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[701]);         
THIS= (ParallelPeriod([Zeit].[Hierarchie J-M-T].[Jahr], 2, [Zeit].[Hierarchie J-M-T].CurrentMember),[Zeitanalyse].[Zeitanalyse].[Element].&[101]);          
END SCOPE;

Because of Corona, we want to fix 2019 = [Zeit].[Hierarchie J-M-T].[Jahr].&[2019] as the base in both subcubes.
Can you help us to move the currentmember via mdx to the 2019'er pendand?

Thanks a lot!

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,344 questions
{count} votes

5 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-12-24T09:06:52.387+00:00
     -- 602 VVJT (VVJT)
     SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[602]);     --gw 16.10.14
     THIS= 
         Iif([Zeit].[Hierarchie J-M-T].CurrentMember.Level.Ordinal<>3,Null,
            [Zeit].[Hierarchie J-M-T ].CurrentMember.Lag( ([Zeit].[Jahr].CurrentMember.Member_Key - 2019) * 364 )
            ,[Zeitanalyse].[Zeitanalyse].[Element].&[101])
         );    
     END SCOPE;    
     -- 701 VVJP (VVJP)
     SCOPE ([Zeit].[Hierarchie J-M-T].[Tag], [Zeitanalyse].[Zeitanalyse].[Element].&[701]);         
     THIS= (Cousin ( [Zeit].[Hierarchie J-M-T ].CurrentMember, [Zeit].[Hierarchie J-M-T].[Jahr].&[2019] ), [Zeitanalyse].[Zeitanalyse].[Element].&[101]);          
     END SCOPE;
    

  2. Refa Abay 1 Reputation point
    2021-12-24T11:21:43.143+00:00

    Hi @Alexei Stoyanovsky ,

    but unfortunately no more results come. Do you possibly have any more ideas about this?
    The cube has not(!) been default processed again. Can this be related to that?


  3. Refa Abay 1 Reputation point
    2021-12-24T13:20:56.003+00:00

    My mistake. When copying the hierarchy name, I stupidly produced a space, which made the element unreachable.
    Now it works.
    Thank you @Alexei Stoyanovsky once again very much.

    But unfortunately there is a problem with the content.
    After all, the purpose of element 602 is to provide a weekday comparison.
    I.e. what we want:
    in 01.06.2021 (Tuesday) for element 602 from (weekday) Tuesday, 04.06.2019 (26.202,42)
    but now we get the date comparison for both elements:
    in 01.06.2021 (Tuesday) for element 602 (80,572.32) from (date) Saturday, 01.06.2019 (80,572.32).

    The calculation Lag(728) always resulted in exactly the same weekday in the previous year. Lag(324) the same weekday last year, etc.


  4. Refa Abay 1 Reputation point
    2021-12-29T22:54:21.437+00:00

    Dear @Alexei Stoyanovsky ,

    I'm afraid I can't finish and I urgently need help again.
    With your kind help, we had set 2019 as the base year.
    But the slope in percent 703 VVJP_delta_p (VVJP ? %) is unfortunately not correct, although the calculation seems to work.
    The error occurs on MONTH and YEAR level. On the DAY level of element 703 the calculation for the slope works.

    Via Mdx (ssms mdx query) I also get correct results, but as soon as I create them via SCOPE something is added up that I unfortunately can not understand.

    This is what we (in scope for 703) need:

    Can you please help again?
    Thank you very much in advance.
    Refa

    Not working correctly (month and year level)
    I have tried everything

    Here the three scopes. The 701 and 702 are working good at any level.
    But the 703 ist not working on the levels month and year correct. Only the day level looks good.


  5. Refa Abay 1 Reputation point
    2022-01-03T13:26:12.897+00:00

    Hi @Alexei Stoyanovsky ,

    do you have an idea how I can add up this element that performs calculations at the day level?
    It is always enough only the month level, but still can not simply add the monthly values, but exactly the element "[Time analysis].[Time analysis].[Element].&[602]" returned at the day level must be aggregated.
    If you could help me with this, I would be very grateful.

    161880-image.png


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.