none
MDX _ LASTPERIODS isn't working properly RRS feed

  • Question

  • Hello,

    I want to add a add a new calculated member to my cube that returns the values of a measure in the last 12 months.

    For that I have created the following member: 

    (LASTPERIODS(12,[MONTH].[Hiérarchie].currentmember), [Measures].[GSO STD]) but it always returns null values 

    However,  when I test it on SSMS on a specific dates I have  LASTPERIODS(4,[MONTH].[Hiérarchie].[MONTH ID].&[201712]), I do have values. And then I use the same data context on my SSAS cube browser and I have null values. 

    Could you please let me know what I could be doing wrong ?

    Thank you very much 

    Mariem,

    Wednesday, November 6, 2019 8:23 AM

Answers

  • Hi Mariem,

    Thanks for your detailed description.

    The MDX expression could be like this.

     CREATE MEMBER CURRENTCUBE.[Measures].[Named Measure]
     AS
     SUM(LASTPERIODS(12,[MONTH].[Hiérarchie].currentmember), [Measures].[GSO STD])

    Best Regards,

    Will


    MSDN Community Support
    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 MSDNFSF@microsoft.com.

    • Marked as answer by MariemArfaoui Tuesday, November 12, 2019 4:14 PM
    Monday, November 11, 2019 8:07 AM

All replies

  • Hi Mariem,

    Thanks for your question.

    >>For that I have created the following member: 

    >>(LASTPERIODS(12,[MONTH].[Hiérarchie].currentmember), [Measures].[GSO STD]) but it always >>returns null values 

    Do you mean to aggregate the values of [Measures].[GSO STD] for the last 12 months? If not, what is the format of your output like?

    Currentmember couldn't be used like that, maybe you need to scope statement to achieve it, but I'm not sure your final requirement.

    Best Regards,

    Will


    MSDN Community Support
    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 MSDNFSF@microsoft.com.

    Thursday, November 7, 2019 6:51 AM
  • Hey Mariem

    The default member for your [Hiérarchie] is probably the All member, it can't work with LASTPERIODS .

    You have to define a member (as you done successfuly in the SSMS) .

    Try to wrap your code with a scope containing a month and below level :

    Scope([MONTH].[Hiérarchie].ALL, [MONTH].[Hiérarchie].Month, SELF_AND_AFTER);

    SUM{(LASTPERIODS(12,[MONTH].[Hiérarchie].currentmember), [Measures].[GSO STD])};

    End Scope;


    Regards, David .

    Thursday, November 7, 2019 7:27 AM
  • Hello Will,

    Thank you for taking the time to help me 😊

    Let me first explain to you my context . I’m actually connecting my cube to PowerBI and I would like to produce a chart that according to the maximum date of my calendar, i want to produce a chart that shows  [Measures].[GSO STD] for each month in the last  24 months.

    For instance, my query should return something like this (for instance in case i choose to roll back 4 months): 

    MonthID     Product    GSO_STD

    20170801        A            700

    20170901        A           1000

    20171001        A           1055

    20171101        A           800

    20171201        A           1000   

    When using DAX i created this measure :

    UN_EQ_24M =

    var currentdate = max(DateDimForGraph[MONTHDAY_DT])

    var previousdate = DATEADD(DateDimForGraph[MONTHDAY_DT];-N[N Value];MONTH)

    var UN_EQ_24M =

        CALCULATE(sum(FACT_SOG[GSO_STD]);

                KEEPFILTERS(FACT_SOG[MONTHDAY_DT] >= previousdate && FACT_SOG[MONTHDAY_DT] <= currentdate))

    return

    UN_EQ_24M

    As you can see that with powerBI/DAX, I allow the user to specify the n° of months to roll back however with MDX, I’m okay to setting that to fixed 24 months.

    Regards,

    Mariem

    Thursday, November 7, 2019 11:59 AM
  • Hello David,

    Thank you for taking some of your time to help me ! 

    When try to add a calculated member using scope, I get an error ; Syntax for SCOPE is incorrect.

    I even wrote something very simple (as below) just to test it and I still get the same error.

    Scope([Measures].[GSO STD]);
        this=1;
    END SCOPE;

    Thursday, November 7, 2019 12:03 PM
  • Hi

    Do you have a CACULATE; at the beginning of your calculations ?

    **Will advise you to use BIDSHelper, so it will be easier and safer to deploy only the calculations, and not the whole solution .


    Regards, David .

    Thursday, November 7, 2019 12:16 PM
  • Hello,

    Yes, I do, I already have a list of measures that are working. But it seems that everytime I add the scope, it just messes up and I need to go the script view and remove my added calculation

    Thursday, November 7, 2019 12:41 PM
  • Hi Mariem,

    Thanks for your detailed description.

    The MDX expression could be like this.

     CREATE MEMBER CURRENTCUBE.[Measures].[Named Measure]
     AS
     SUM(LASTPERIODS(12,[MONTH].[Hiérarchie].currentmember), [Measures].[GSO STD])

    Best Regards,

    Will


    MSDN Community Support
    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 MSDNFSF@microsoft.com.

    • Marked as answer by MariemArfaoui Tuesday, November 12, 2019 4:14 PM
    Monday, November 11, 2019 8:07 AM
  • Thank you Will, it works !
    Tuesday, November 12, 2019 4:14 PM