Forum Discussion

Ven's avatar
Ven
Advocate I
9 years ago

DAX Query to Fitler rows by Date

Hi

 

I'm connecting to SQL Server Analysis Services Importing Data and it's returning rows that are several years old and I only need data for the last year. The connection screen for Analysis Services has a section for entering MDX or DAX. From the Source I have a Month and Year column to work with.

 

First can I use a DAX query to filter the rows returned to be within the last twelve months?

 

I have tried the following DAX query in the query input box but I get the error it is not a valid MDX or DAX query 

 

FILTER(Sales, DATE('01', Sales[Month], Sales[Year]) <= EDATE(TODAY(), -12))

 

Any help or information gratefully received

Thanks

Ven

 

Need Help
Tips and Tricks
Tutorial Requests

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You might need to format it as a Dax "Query" vs. the expression language you would use in a measure.  Here is an example coded against a date dimension.

     

    evaluate
(
   filter
   (
     'Date',
     'Date'[Date] >= EDATE(Today(),-24) 
   )
)

     

    • Ven's avatar
      Ven
      Advocate I
      9 years ago

      Hi

       

      Thansk for the reply Eskyline

       

      I did also find out that the DATE() function in my formula has the year and day the wrong way round as well so it should eb like this DATE(<Year>, <Month>, <Day>). I'll give your suggestion a go and see how I get on with it.

       

      Thanks

      Ven

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Ven,

    Please use double quotation marks instead of single quotation marks to enclose 01 in your scenario. The formula in your scenario should be as follows.

    evaluate
(
   filter
   (
     'Sales',
     date('Sales'[Year],'Sales'[Month],"01") <= EDATE(Today(),-12)
   )
)



    Thanks,
    Lydia Zhang

    • Ven's avatar
      Ven
      Advocate I
      9 years ago

      Hi

       

      I've tried that DAX query and it give me an error about not being able to resolve the table 'Sales'. This is probably because it's the top level of the Analysis Cube. Not sure how to proceed from here.

       

      Thanks

      Ven

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Ven,

        Have you firstly ran the above query in SQL Server Management Studio? Does it return expected result from SSAS database?

        Thanks,
        Lydia Zhang