Get Data using MDX where date is less than

Question

I have query where I take double values and then dates (e.g. 2020-03) . This is how it looks:

SELECT 
  NON EMPTY 
    {[Measures].[Revenue]} ON COLUMNS
 ,NON EMPTY 
    {[DimDates].[YearMonthNum].[YearMonthNum].ALLMEMBERS}
  DIMENSION PROPERTIES 
    MEMBER_CAPTION
   ,MEMBER_UNIQUE_NAME
   ON ROWS
FROM 
(
  SELECT 
    {[TransactionsData].[FirstYearMonth].&[ALL]} ON COLUMNS
  FROM 
  (
    SELECT 
      {[TransactionsDataExtra].[Cluster].[All]} ON COLUMNS
    FROM 
    (
      SELECT 
        {[TransactionsDataExtra].[Campaign].[All]} ON COLUMNS
      FROM 
      (
        SELECT 
          {[TransactionsData].[RevenueGenerating].[All]} ON COLUMNS
        FROM 
        (
          SELECT 
            {[TransactionsData].[Product].[All]} ON COLUMNS
          FROM 
          (
            SELECT 
              {[TransactionsDataExtra].[Channel].[All]} ON COLUMNS
            FROM 
            (
              SELECT 
                {[TransactionsDataExtra].[Custom4].[All]} ON COLUMNS
              FROM 
              (
                SELECT 
                  {[TransactionsDataExtra].[Custom5].[All]} ON COLUMNS
                FROM 
                (
                  SELECT 
                    {[TransactionsDataExtra].[Custom6].[All]} ON COLUMNS
                  FROM 
                  (
                    SELECT 
                      {[TransactionsData].[ClientId].&[233]} ON COLUMNS
                    FROM [Model]
                  )
                )
              )
            )
          )
        )
      )
    )
  )
)
WHERE 
  (
    [TransactionsDataExtra].[Channel].[All]
   ,[TransactionsData].[Product].[All]
   ,[TransactionsData].[RevenueGenerating].[All]
   ,[TransactionsDataExtra].[Campaign].[All]
   ,
    [TransactionsData].[FirstYearMonth] <= '2020-01'
   ,[TransactionsDataExtra].[Cluster].[All]
   ,[TransactionsData].[ClientId].&[233]
  )
CELL PROPERTIES 
  VALUE
 ,BACK_COLOR

what I am trying to achieve is to filter this query by single date if any is selected, e.g.

[TransactionsData].[FirstYearMonth] <= '2020-01'

but I get error: The function expects a tuple set expression for the 5 argument. A string or numeric expression was used.

I also tried passing date like:

[TransactionsData].[FirstYearMonth] <= ['2020-01'] but that also didn't work.

I am new to DAX and I kind of tried to apply what I would use if I used sql, which obviously isn't the right way. Could anyone help me out?

user_1179880 · Answer

You can use the operator : to define a date range.

So in Adventure Works this is valid:

{[Date].[Date].&[20130101]:[Date].[Date].&[20130131]}

Now if you leave one side as null then it will mean up to and including the date on the right hand side. So this is valid:

select 
[Measures].[Internet Sales Amount]
on columns
from [Adventure Works]
where {null:[Date].[Date].&[20130131]}

So your WHERE needs adjusting to something like:

WHERE 
  (
    [TransactionsDataExtra].[Channel].[All]
   ,[TransactionsData].[Product].[All]
   ,[TransactionsData].[RevenueGenerating].[All]
   ,[TransactionsDataExtra].[Campaign].[All]
   ,{null:[TransactionsData].[FirstYearMonth].&[20200101]}
   ,[TransactionsDataExtra].[Cluster].[All]
   ,[TransactionsData].[ClientId].&[233]
  )

In the above you'll need to make sure that this expression [TransactionsData].[FirstYearMonth].&[20200101] is a member in your cube.

Get Data using MDX where date is less than

1 Answers