Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ven
Frequent Visitor

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

 

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia

 

Sorry to say I've not had the time to try this out and have moved on to other projects. When I get a chance I'll give this go.

 

Thanks for your reply and time

Ven

 

eskyline
Resolver I
Resolver I

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
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors