Sql-server – How to get the measures of the last month with MDX

dimensional-modelingmdxsql serverssas

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.

Best Answer

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

Related Solutions

ParallelPeriod returning null for Feb. 29 in date dimension

Another option would be to use a union to get parallel periods for the current and previous member. Then extract the first item out of this, which will always be the current member (except in the case of Feb 29 where it should fall back to the previous member).

That is, effectively coalesce with the previous member parallel period as per below:

union 
(
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember) 
    *(will be null if Feb 29)*
    ,
    PARALLELPERIOD(
        [Date].[Fiscal Detail].[Fiscal Year],1,
        [Date].[Fiscal Detail].currentmember.lag(1))  
    *(will be the prior year Feb 28 when above is Feb 29 )*    
).item(0)  *(get the first member out of the union set)*

Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTS

I tried to duplicate what you have using a multidimensional Contoso cube with a measure that is Min Inventory Day in Stock and didn't encounter your issue. I think it has to do with the current context when you aggregate [adhoc]. I don't have enough info to duplicate exactly. But this phrase caught my eye when looking at the specifications for MIN():

If a numeric expression is not specified, the specified set is evaluated in the current context of the members of the set and then returns the minimum value from that evaluation.

First, a few thoughts:

As a note, you don't need single quotes around your calculated members in your query. As long as your calculation doesn't throw an error, it doesn't hurt for them to be there, but they aren't needed after SSAS 2005 or so and can suppress messages in some cases.
Calculated members are evaluated at run time so you can't take advantage of any caching. It's better to move the work into a scoped script and keep the calculated measure simple. Or move them to into the axes if possible so they can be cached with the query as it is run multiple times. If you have a large dataset or very critical data that must be returned quickly you might want to change your use of calculated members.
Your UNION with DESCENDANTS is fine. I was able to do something similar and it produced the correct results. You can check this by using the set instead of the aggregated member and confirming that all the members are present as you expected. I'm a little puzzled why you have it, though. Correct me if I'm wrong, but I thought Wesser Ems is part of Lower Saxony (I'm not completely knowledgeable on German geography, though). If Wesser Ems is part of Lower Saxony, you could have just done the descendents of Lower Saxony and not done the union. That is neither here nor there since there is nothing wrong with your union. I just like to try to simplify queries as much as possible.
SSAS is really forgiving. It will try to interpret what you mean when you don't specify a fully qualified member. For instance, you use [Has Data] rather than [Measures].[HasData], and your set and aggregated member are both [adhoc]. If there is also a field called [HasData] somewhere, it can get confused as to what you meant.
SSAS ignores nulls when calculating the min
You already have a measure that performs the MIN() ([Measures].[Has Data]), so using AGGREGATE() will continue to do the min, if that is what is in the current context.
Unlike the WHERE clause in SQL, the WHERE clause of an MDX SELECT statement never directly filters what is returned on the Rows axis of a query. To filter what appears on the Rows or Columns axis of a query, you can use a variety of MDX functions, for example FILTER, NONEMPTY and TOPCOUNT. For instance, if I don't have any inventory in 2006, 2006 is still returned as a member in the following query:

WITH SET [adhoc] AS 
UNION(DESCENDANTS([Geography].[Geography Hierarchy].[Region Country Name].[Germany], 
[Geography].[Geography Hierarchy].[City Name]), 
DESCENDANTS([Geography].[Geography Hierarchy].[State Province Name].[Lower Saxony], 
[Geography].[Geography Hierarchy].[City]))
MEMBER [Geography].[Geography Hierarchy].[adhoc] as MIN([adhoc])   
SELECT {[Date].[Calendar YQMD].[Calendar Year].members } on 0 
from [Operation]
where ([Geography].[Geography Hierarchy].[adhoc],[Measures].[Inventory Min Day In Stock])

In my dataset, my fact table only has a value of 5 for the data that makes up [Measures].[Inventory Min Day In Stock]. This is similar to you having all 1s.

This query returns the same expected answer (5) for me whether I use MIN([adhoc]) or AGGREGATE([ad hoc])

WITH 
SET [adhoc1] AS 
UNION(DESCENDANTS([Geography].[Geography Hierarchy].[Region Country Name].[Germany], [Geography].[Geography Hierarchy].[City Name]), 
DESCENDANTS([Geography].[Geography Hierarchy].[State Province Name].[Lower Saxony], [Geography].[Geography Hierarchy].[City]))
MEMBER [Geography].[Geography Hierarchy].[adhoc] as Min([adhoc1])
SELECT {[Date].[Calendar YQMD].[Calendar Year].[Year 2008] } on 0
from [Operation]
where ([Geography].[Geography Hierarchy].[adhoc],[Measures].[Inventory Min Day In Stock])

My best advice is to try rewriting it a couple of different ways. If the calculated measures aren't too slow, you can use them to make sure you have the correct context.

WITH 
MEMBER [Geography].[Geography Hierarchy].[adhoc] as Aggregate(UNION(DESCENDANTS([Areas].[Hierarchy].[Region].[Weser Ems],[Areas].[Hierarchy].[City]),DESCENDANTS([Areas].[Hierarchy].[State].[Lower Saxony],[Areas].[Hierarchy].[City])))
MEMBER [Measures].[Test] as Min([Geography].[Geography Hierarchy].[adhoc],[Measures].[HasData])
SELECT {[Time].[Hierarchy].[Year].[2008] } on 0
from [Incidence]
where ([Measures].[Test])

Best Answer

Related Solutions

ParallelPeriod returning null for Feb. 29 in date dimension

Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTS

Related Question