Date range using DAX query in SSRS

Question

I am using SSRS 2015 to create a report. The data is sourced from a tabular cube so I used a DAX query to create the shared dataset. What I am trying to do is add parameters to my report which would allow users to filter the data according to their date range using the calendar picker. I tried:

EVALUATE
FILTER(
SUMMARIZE(
  'PurchaseTable'
  ,'PurchaseTable'[Invoice Date]
),
  'PurchaseTable'[Invoice Date] >= DATEVALUE(FORMAT(@FromDate, "dd/MM/yyyy"))
  && 'PurchaseTable'[Invoice Date] <= DATEVALUE(FORMAT(@ToDate, "dd/MM/yyyy"))
)

But it produced an error: The following system error occurred: Type mismatch

I also tried:

  'PurchaseTable'[Invoice Date] >= @FromDate
  && 'PurchaseTable'[Invoice Date] <= @ToDate

and get the following error: DAX comparison operations do not support comparing values of type Date with values of type Text

My PurchaseTable[Invoice Date] column is of date type in dd/MM/yyyy format. Thank you in advance for any help.

ViKiNG ViKiNG · Accepted Answer · 2018-01-05T03:38:39

If you are comfortable with adding parameters to DAX after playing around with SSRS, the following should be the code for your final DAX.

EVALUATE(
FILTER(
SUMMARIZE(
  'PurchaseTable'
  ,'PurchaseTable'[Invoice Date]
),
  'PurchaseTable'[Invoice Date] >= VALUE(@FromDate)
  && 'PurchaseTable'[Invoice Date] <= VALUE(@ToDate)
))

Make sure that your parameters are always of date type.

Hope this help?

Date range using DAX query in SSRS

1 Answers