4 Replies Latest reply on Jul 5, 2016 8:29 AM by priyanka reddy

    Need to calculate change and change percentage for Latest Month in MDX

    priyanka reddy

      Hello,

      I am connecting to a OLAP Cube (Microsoft Analysis Services). Please see attached document for the sample cube data.

      I have a cross tab of product categories (in rows) and Months (in columns). As you can see, my months range between February 2007 to October 2010.

       

      Now, I am trying to calculate Change and Change % for the latest month (as compared to previous month). So, in my example, latest month would be October 2010 and previous month would be September 2010.

      I am not able to figure out how to calculate change and change % for Cube Data.

       

      Best Regards,

      Priya

        • 1. Re: Need to calculate change and change percentage for Latest Month in MDX
          Ashish Chaudhari

          Hi Priyanka,

           

          is it possible to extract data and share the twbx file since I m getting driver errors. Can't install that.

           

          Thanks and Regards,

          Ashish Chaudhari

          • 2. Re: Need to calculate change and change percentage for Latest Month in MDX

            In order to be able to do this we first need to understand some basic MDX concepts. First of all, in order to be able to uniquely identify a member on a dimension we need to know its 'unique name': for example [Time].[All Years].[2005] or [Product].[All Products].[Classic Cars]. We can then use one or more unique names as co-ordinates to identify individual cells within the cube, and one of these cell references is called a 'tuple'. A tuple in MDX is written as a comma-delimited list of member unique names enclosed in parentheses, for example ([Measures].[Sales], [Time].[All Years].[2005], [Product].[All Products].[Classic Cars]). Tuples must contain at least one member unique name and can contain up to however many unique names as there are dimensions in the cube; in the latter case you could be sure to have a static reference to just one cell in the cube but most of the power of tuples in MDX comes from being able to only refer to members on a few dimensions. When you do this, the members on the dimensions you don't explicitly refer to are either supplied by the context of the query or, if the dimensions aren't referred to in the query either, by the 'default member' settings on the dimension

             

             

             

            Create Member CurrentCube.[Measures].[Internet Sales Amount - Prior Year] As

                (

                    ParallelPeriod(

                         [Date].[Calendar].[Calendar Year]

                        ,1

                        ,[Date].[Calendar].CurrentMember

                    )

                    ,[Measures].[Internet Sales Amount]

                )

            ,Format_String = "Currency"

            ,Associated_Measure_Group = 'Internet Sales';

            Create Member CurrentCube.[Measures].[Internet Sales Amount - YoY Change] As

                (

                    [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount - Prior Year]

                )

            ,Format_String = "Currency"

            ,Associated_Measure_Group = 'Internet Sales';

            Create Member CurrentCube.[Measures].[Internet Sales Amount - YoY Pct Change] As

                (

                    [Measures].[Internet Sales Amount - YoY Change]

                    /

                    [Measures].[Internet Sales Amount]

                )

            ,Format_String = "Percent"

            ,Associated_Measure_Group = 'Internet Sales';

            For more doubts you can access the below link

            • 3. Re: Need to calculate change and change percentage for Latest Month in MDX
              priyanka reddy

              Ashish,

              Kindly download the drivers, it would be very easy to replicate my situation and find a solution.

               

              I am still trying to find a solution for this problem of calculating change and change percent.

               

               

              Best Regards,

              Priya

              • 4. Re: Need to calculate change and change percentage for Latest Month in MDX
                priyanka reddy

                Remya,

                I used export to Access Data Connection and worked on the exported data. I think for now, I am getting solutions, I am still not sure if the data changes, how my dashboard will behave.