How to write MDX formula for a filter

Question

I am working on a report in BI that includes 2 datasets. The screenshot below is of DataSet2 where I am trying to write a MDX filter expression for the query. What is the formula so that the query only shows rows from the past 2 months from today. I tried a few different formulas…[GamingDay].[Date] > CDate(DateAdd(‘m’,-2,Now())) and it returns an error. What is the correct formula to do this?

enter image description here

Are you able to write MDX with a hardcoded date such as 1 January 2019 first? Do that before trying the CDate() expression, and let us know what your full MDX query was. — Magnus Smith

MoazRub MoazRub · Accepted Answer · 2019-03-17T09:29:24

You need to use double quotes to resolve the issue. Take a look at the sample below

with member measures.t 
as 
CDate(
DateAdd("m",-2,Now())
)
select 
{[Measures].[Internet Sales Amount],measures.t }
on columns,
[Product].[Category].[Category]
on rows
from 
[Adventure Works]

Result

How to write MDX formula for a filter

1 Answers