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