none
Pull last 2 months this year and last year using MDX RRS feed

  • Question

  • i am trying to pull last 2 months data and last 2 months this time last year but i can't get the numbers to match up. Is there an easy way to pull last 2 months and the same dates for last year. I have tried to lag the days but it doesnt match up is there a more sensible way to do it?


    • Edited by dataman123 Tuesday, June 30, 2020 8:14 PM
    Tuesday, June 30, 2020 6:22 PM

All replies

  • Hi Dataman,

    Are you referring to last 60 days' data ?  How looks like your expression?

    You could try PARALLELPERIOD() function

    For example:

    with member [last 60 days Orders] as
    sum(
    [Date].[Date].currentmember.lag(60):[Date].[Date].currentmember
    ,[Measures].[Internet Sales Amount])
    
    
    member [Last year comparison] as
    sum(ParallelPeriod ([Date].[Calendar].[Calendar Year] ,1, [Date].[Calendar].currentmember).lag(60)
    : ParallelPeriod ([Date].[Calendar].[Calendar Year], 1 ,[Date].[Calendar].currentmember )
    ,[Measures].[Internet Sales Amount])
    
    
    SELECT {[last 60 days Orders],[Last year comparison]} on 0,
    [Date].[Calendar].[Date] on 1
    FROM [Adventure Works]

    If this not works for you, please share you query or error message or description so we could work on it.


    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.


    Wednesday, July 1, 2020 7:26 AM
  • Hi thanks for the response, so at the moment i am pulling the data for this year and last year separate then appending them via the following MDX. But what i want to be able to do is pull all together as 1 but pull this year 60 days last year same 60 days;

    SELECT NON EMPTY { [Measures].[Value],[Measures].[ Units], [Measures].[INI] } ON COLUMNS, NON EMPTY {({STRTOMEMBER(""[Date].[Date].&[""+FORMAT(NOW(),""yyyyMMdd"")+""]"").Lag(60):STRTOMEMBER(""[Date].[Date].&[""+FORMAT(NOW(),""yyyyMMdd"")+""]"")} * [Top].[Top].[Top].ALLMEMBERS * [ID].[ID].[ID].ALLMEMBERS * [Invoice].[Invoice].[Invoice].ALLMEMBERS * [Region].[Region].[Region].&[South - Parent] ) } ON ROWS FROM [Sales]

    Wednesday, July 1, 2020 10:32 AM
  • >>>>"pull all together as 1"

    You could use "+" operator to combine the two sets in you query in this part:

    ...{({STRTOMEMBER(""[Date].[Date].&[""+FORMAT(NOW(),""yyyyMMdd"")+""]"").Lag(60):STRTOMEMBER(""[Date].[Date].&[""+FORMAT(NOW(),""yyyyMMdd"")+""]"")}...

    Outside the curly bracket "+" last year 60 days set, so they would be one.

    Check the reference. Combine sets.


    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, July 2, 2020 1:57 AM