MDX query to get all dates where dateis greater than

Question

For example i am need to take all dates where year is greater than 2013. This code return all dates.

SELECT 
  {[Measures].[In]} ON COLUMNS
 ,Filter
  (
    [Date].MEMBERS
   ,
    [Date].[Year].CurrentMember > [Date].[2013]
  ) ON ROWS
FROM [Shop];

And i am need to take all dates where date is greater then 2013.05.20

whytheq · Accepted Answer · 2016-08-08T12:45:58.343

6

If you want to do greater than within a range then use null on one side:

SELECT 
  {[Measures].[In]} ON 0,
  {[Date].[Year].[2013] : NULL}  ON 1
FROM [Shop];

edited Aug 08 '16 at 12:45

answered Aug 04 '16 at 14:12

whytheq

34,466
65
172
267

Mondrian return empty – Nodon Aug 08 '16 at 09:26

score 0 · Answer 2 · answered Aug 04 '16 at 12:27

0

Your query should be correct. I tried a similar query in my cube:

SELECT NON EMPTY { [Measures].[OneMeasure] } ON COLUMNS,
NON EMPTY
{
 FILTER( [Date].[Year].[Year].AllMembers, [Date].[Year].CurrentMember < [Date].[Year].&[2015])
} ON ROWS
FROM [Cases]

And it works as expected. The date dimension should be properly created in order for the comparison to work properly. If you want specific date you should use the Date level and not the Year.

answered Aug 04 '16 at 12:27

Veselin Davidov

7,031
1
15
23

Mondrian server compare a measure for date with measure not a date with date. :-) – Nodon Aug 04 '16 at 12:59
1

this is not very standard mdx using a `>` operator with expressions that are Member typed on either side. – whytheq Aug 04 '16 at 14:12

score 0 · Answer 3 · answered Jun 18 '21 at 10:04

The below query gives past 2 years data based on Start Date field.

SELECT 

{[Measures].[Measure_1],

[Measures].[Measure_2]
      } 
ON 0, 
       NON EMPTY{
       FILTER([Data].[Start Date].[Start Date].Members,
[Data].[Old Start Date].CurrentMember.MemberValue > 
DateAdd("m",-24,Now())
)
}
ON 1 
FROM   [Model]

MDX query to get all dates where dateis greater than

3 Answers3