Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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
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
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) ) )
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