4

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.

4
+50

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'

enter image description here

This is the result of the code:

enter image description here

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

| improve this answer | |
  • The sctucture and the relationships in my time dimension is like AdventureWorksDW project sample. What do you need to know about my data? I'll try to do it with your MDX and I'll let you know something – Miguel Oct 16 '15 at 10:21
  • 1
    It gave me an error in Rows, it says members are from differents hierarchy in the PARALLELPERIOD function. – Miguel Oct 16 '15 at 15:18
  • how can I let you know here, my calendar hierachy? – Miguel Oct 19 '15 at 16:09
  • The easiest would be if you can show expected output using the adventureworks cube – Tom V - try topanswers.xyz Oct 19 '15 at 16:15
  • @Miguel I think the main question is what you are doing the tail for, if you can explain that I think I can make something up using adventureworks – Tom V - try topanswers.xyz Oct 20 '15 at 10:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.