Skip to content
January 20, 2013 / Erik Svensen

Using Cube functions and MDX to find the last period with sales

The Excel cube functions in combination with PowerPivot is very useful when you design dashboards in Excel.

The Cube functions enables you to retrieve sets, members and values from the PowerPivot model and you can use your knowledge about MDX to make them even more powerful.

In this post I will demonstrate how to find the last month with sales and then use that member to create a sparkline or chart to show the last 12 months of sales.

The example is built on the AdventureWorkDW2012 sample database from CodePlex.

I have built a Year month hierarchy based on the calendar year and a month label (Calendar year + month number)

First I use the CUBESET function to calculate all month with sales.

=CUBESET(“ThisWorkbookDataModel”;”FILTER([DimDate].[CYM].[Month label].MEMBERS,[Measures].[Sum of SalesAmount])”;”Periods with sales”)

The first argument refers fo the powerpivot model (remember the new name in Excel 2013). The second argument is a MDX expression that filters all the members from the [Month label] members in the Year month hierarchy. The third argument is not needed but handy to show give the cell a visible feedback.

Then I use CUBESETCOUNT function to calculate the number of month with sales

=CUBESETCOUNT(H7)

This will return 36 which is the number of month with sales.

To retrieve the last month with sales I then use the CUBERANKEDMEMBER

=CUBERANKEDMEMBER(“ThisWorkbookDataModel”;”FILTER([DimDate].[CYM].[Month label].Members, [Measures].[Sum of SalesAmount])”;H9)

This will return the last member ie the last month of sales as the third argument refers to the CUBESETCOUNT result.

 

To create a dynamic area with the last 12 month of sales you can build a table like this

 

The formula to retrieve the sales value for the month can then be calculated like this

=CUBEMEMBER(“ThisWorkbookDataModel”;”[DimDate].[CYM].[“&$H$11&”].lag(“&T16&”)”)

Where the lag expression can retrieve period – x periods compared to the last sales period.

And then retrieve the Sales Value by referring to the members and slicer if you want.

=CUBEVALUE(“ThisWorkbookDataModel”;T17;$H$18;Slicer_SalesTerritoryCountry)

And remember that you can refer to Measures as Members so you can make your dashboard very flexible.

You can download the example file from here

Advertisements

5 Comments

Leave a Comment
  1. David Hager / Jan 21 2013 2:08 am

    File too big to download from SkyDrive.

    • Erik Svensen / Jan 21 2013 8:49 am

      Hi David,

      Thx for the heads up … I have deleted some data from the model so its size is now 4 mb

      /Erik

  2. Andrew Norris / May 11 2013 1:48 pm

    Hi Eric,

    Another well explained and detailed article which I have used.

    Using standard excel data validation technique I have added a drop down list to cell C2,
    and now have a table that can slice by FY, QuaterPeriod etc.

    Example of modified cell

    =CUBESET(“ThisWorkbookDataModel”,”FILTER([QDate].[“&+C2&”].MEMBERS,[Measures].[IssuedCertCount])”,”Periods with certs”)

    Many thanks for this and the other articles.

    Andy

    • Erik Svensen / May 11 2013 7:55 pm

      Hi andrew,

      Nice addition to the example.

      Glad to hear that my posts is useful

      /Erik

      • Pat / Jan 23 2014 12:58 pm

        hi erik,
        can you give us the sample if we use drop down list as well?
        tks,
        pat

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: