How can I get the measures of the last month with MDX?

Question

I would like to get the values of the measures of the previous month.

That's what I have:

SELECT
   [Measures].[Recuent distinct] ON ROWS,
   TAIL(NonEmptyCrossJoin([Dim Date].[Calendar].[Month Name].Members,1),1) ON     COLUMNS
  FROM [BDID]
  WHERE [Dim Canal].[Canal].&[Data]

Result:

October 2015
    Recuent Customers 10344

That's what I would like to have:

September 2015
Recuent Customers 20754

The thing is that my MDX takes the values of the current month, but I want to take the values of the last month. It has to be valid for each month, I mean I would like to have an automatic query for using on Power BI. It means, this month It has to gave me the values of the last month (September) and next month the values of October, in order to avoid the modification of the query each month.

Let me know if you need more information.

Tom V · Answer 1 · 2015-10-20T18:06:42.843

Without sample data or structure it would be hard to provide the exact code, but here goes.

You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and navigates back a number of steps in that level.

Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension with an actual value in your measure.

I whipped up an example in the AdventureWorks database that I think is getting you what you want (which still isn't entirely clear)

WITH MEMBER [Measures].[LastmonhtValue]
as
sum(PARALLELPERIOD([Date].[Calendar].[Month],1),[Measures].[Customer Count])

SELECT
    [Measures].[LastmonhtValue] ON 0,
 TAIL(NonEmptyCrossJoin([Date].[Calendar].[Month].Members,[Measures].[Customer Count],1),1) on 1 
  FROM [Adventure Works]

The way this works:
- the parallelperiod function moves back one month
- the tail function uses the regular measure to get you the last nonempty month
- the result is 1 month with previous month's data

This is how the data looks when you browse the cube using 'month' and 'customer count'

This is the result of the code:

Barring any additional information from your side I'm afraid that is the best I can do.

Be sure to design your attribute relationships in your time dimension to make sure this code has acceptable performance

How can I get the measures of the last month with MDX?

1 Answers1