Excel MDX expression filter for Same Period Last Month

Question

I have managed to create a filter for OLAP Cube for the 'last 30 days' as described here: http://blogs.socha.com/2010/05/sliding-date-ranges-with-excel-2010.html . Now I would need to get the 'Same Period Last Month' filter done the same way (so I can drag and drop it in the pivot table to filter any sales data). Ideally it would show just totals, not day by day data. I tried many different expressions but since I'm very new to MDX nothing works for me.

I would appreciate you help.

Thanks, Maciej

When you say 'Same Period Last Month', the same period like what do you want to see? — FrankPl
Basically if today's date is 25/02/2014, then I would like to see the sales data for the same period of last month being 01/01/2014 - 25/01/2014, ie. first 25 days of pervious month. That way I can compare this month's performance (month-to-date) to the performance of last month for the same number of days (25). — kaiseros

FrankPl FrankPl · Accepted Answer · 2014-02-25T16:09:52

You would probably use something like

Filter( 
    [Date].[Date].[Date], 
    [Date].[Date].CurrentMember.Member_Value >= 
          DateSerial(Year(DateAdd('m', -1, VBA![Date]())),
                     Month(DateAdd('m', -1, VBA![Date]())),
                     1
                    ) 
    AND [Date].[Date].CurrentMember.Member_Value <= DateAdd('m', -1, VBA![Date]())
)

Here, DateAdd('m', -1, VBA![Date]() is one month before today, which is the end date. And the start date is calculated from this end date using the year and month of this date and 1 as the day within the month, and feeding these into DateSerial to make a date.

See http://technet.microsoft.com/en-us/library/hh510163.aspx for a list of VBA functions (among them many date functions) supported by MDX.

Excel MDX expression filter for Same Period Last Month

1 Answers