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


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


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.


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

5 thoughts on “Using Cube functions and MDX to find the last period with sales

  1. 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.


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s