mdxscript(model) (145,206)

[RMV]

Hi,

I have this graphic that shows error if I set my month slicer to 10-12, but it works perfect if I set month slicer below 10.

I'm using the same value used in the graphic for some other graphic, and it works fine.

The error message is:

"Can't load the data for this visual

MdxScript(Model) (145, 206) Calculation error in measure 'Table'[Amount]: An argument of function 'DATE' has the wrong data type or the result is too large or too small."

The formula I'm using:

Amount PY = CALCULATE(SUM(Table[Amount]),FILTER(ALLSELECTED(Table),AND(Table[Transaction Date]>=DATE(YEAR(TODAY())-1,1,1),Table[Transaction Date]<=DATE(YEAR(TODAY())-1,MONTH(MAX(Table[Transaction Date])),DAY(MAX(Table[TransactionDate]))))))

This is the result if I set the month slicer = 12, where the error occurred

This is the result if I set the month slicer = 9 which show the result perfectly

 

This is the result if I set month slicer to 12, for another category (in this example: 09"), the graphic shows the value fine.

The graphic works well too for the other categories.

Hope that anyone can advise me on what caused the error and how to rectify it.

Thanks before.

[v-chuncz-msft]

@RMV,

You may try adding calculated columns instead of selecting Date Hierarchy.

[RMV]

Hi @v-chuncz-msft,

can you give example, please?

[v-chuncz-msft]

@RMV,

I just meant the following.

[RMV]

Hi @v-chuncz-msft,

I don't use Date Hierarchy for the axis in the graph.

I added a column with Month formula to return the month no. only.

What does this error mean actually, and what is the cause of the error?

[v-chuncz-msft]

@RMV,

Show us a complete example with dummy data so that we might find a workaround.