none
Reporting different periods across years with previous period RRS feed

  • Question

  • Hi,

    we are using Excel as frontend for SSAS. The database contains a cube with a few dimension (including a common time dimension) and several measures. We want to analyze measures (sliced by different dimensions) and filter the time across years. To do this, we select the time dimension as filter and choose some months. For example November 2018 to February 2019.

    The result looks like:

    Customer A       100
    -  Product X         30
    -  Product Y         70
    Customer B       200
    -  Product X         60
    -  Product Z       140

    We now want add a new column and in order to analyze the measure based on the previous period. In this example from November 2017 to February 2018.

    The result should look like:

                               Period 1      Period 2
    Customer A        100             110
    -  Product X          30               40    
    -  Product Y          70               70
    Customer B        200             160
    -  Product X          30                 0
    -  Product Z        170             160

    At the moment, we use two pivot tables with different filters and merge them manually. However, that is very time-consuming because we have many different tables and we have to report monthly.

    Is it possible to create such an report in Excel?

    Thanks in advance.

    Marc

    Thursday, November 7, 2019 1:23 PM

Answers

  • Hi hpo_mh1,

    Thanks for your question.

    Yes, it is possible.

    You could use range operator(:) to meet you needs.

    Firstly, select Analyze menu, then select OLAP Tools, then select MDX Calculated Measure:

    Secondly, type the following MDX expression to the box, rename the member name to "Period 1":

    --Period 1
    SUM([time dimension].[Month].[November 2018]:[time dimension].[Month].[February 2019],[Measures].[OriginalName])
    
    --Period 2
    SUM([time dimension].[Month].[November 2017]:[time dimension].[Month].[February 2018],[Measures].[OriginalName])

    Hope above could be useful to you.

    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 hpo_mh1 Friday, November 8, 2019 3:28 PM
    Friday, November 8, 2019 6:44 AM
  • https://devinknightsql.com/2014/08/14/mdx-time-calculations-built-to-scale/ is a good place to start to understand how the utility dimension works.

    You seem to need a formula close to the one in the latest comment in this thread


    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by hpo_mh1 Wednesday, November 13, 2019 2:17 PM
    Monday, November 11, 2019 8:33 AM

All replies

  • Hi hpo_mh1,

    Thanks for your question.

    Yes, it is possible.

    You could use range operator(:) to meet you needs.

    Firstly, select Analyze menu, then select OLAP Tools, then select MDX Calculated Measure:

    Secondly, type the following MDX expression to the box, rename the member name to "Period 1":

    --Period 1
    SUM([time dimension].[Month].[November 2018]:[time dimension].[Month].[February 2019],[Measures].[OriginalName])
    
    --Period 2
    SUM([time dimension].[Month].[November 2017]:[time dimension].[Month].[February 2018],[Measures].[OriginalName])

    Hope above could be useful to you.

    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 hpo_mh1 Friday, November 8, 2019 3:28 PM
    Friday, November 8, 2019 6:44 AM
  • Ideally this is solved at the cube level with a Time Intelligence utility dimension, a technique amply documented on the Internet. Your Period 1 then becomes the 'Actual' member of this dimension, and Period 2 - 'Same Period Year Back'.

    If you can't change the cube, this can be done in Excel. You'll have to declare a set because you are doing multiselects, and you'll probably end up creating a calculated measure for each existing measure that you need to analyse this way.


    Expect me to help you solve your problems, not to solve your problems for you.

    Friday, November 8, 2019 7:58 AM
  • Hi Will,

    many thanks for answer. With your solution, we have to define a new calculated measure for every measure we want to analyze and we have to modify every measure when we change the time filter, but it will still save us a lot of time. So it’s much better than the way we do it today.

    Best regards,

    Marc

    Friday, November 8, 2019 2:59 PM
  • Hi Alexei,

    yes, we can change the cube. I looked at the time intelligence wizard. That seem to be a good approach. However, the problem is that we want to get a comparison with the applied filter on time dimension. And the filter can be across a calendar year (for example November 2018 to February 2019, or June 2018 to April 2019). I wasn´t able to achieve this. Do you think that’s possible. If yes, could you provide me a link where I can get more information about this topic.

    Many thanks.

    Best regards

    Marc

    Friday, November 8, 2019 3:28 PM
  • https://devinknightsql.com/2014/08/14/mdx-time-calculations-built-to-scale/ is a good place to start to understand how the utility dimension works.

    You seem to need a formula close to the one in the latest comment in this thread


    Expect me to help you solve your problems, not to solve your problems for you.

    • Marked as answer by hpo_mh1 Wednesday, November 13, 2019 2:17 PM
    Monday, November 11, 2019 8:33 AM
  • Thank you Alexei for the links. The content seems to be helpful.
    Wednesday, November 13, 2019 2:17 PM