MdxScript(Model) (8, 67) Calculation error in measure A column specified in the call to function

[Anonymous]

Hi Team,

Am creating new measures based on year column.

Measure 1:

CY Revenue =

var CY = MAX('Sales Data'[Year])

return

CALCULATE(SUM('Sales Data'[Total Revenue]),'Sales Data'[Year]=CY)

Measure 2:

PY Revenue = CALCULATE([CY Revenue],SAMEPERIODLASTYEAR('Sales Data'[Year]))

when am try fetch data from 2nd measure am getting bellow error, please help on same.

[v-rzhou-msft]

Hi  @Anonymous 

The error detail has shown the reason, 'Sales Data'[Year] is not Date type.

Is 'Sales Data'[Year] a Whole number type column with values like 2020, 2021?

Try to update your measure as below.

Measure 2:

PY Revenue = CALCULATE([CY Revenue],Filter(All(Sales Data),'Sales Data'[Year] = MAX('Sales Data'[Year])-1))

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Pragati11]

Hi @Anonymous ,

What is the data-type of your column 'Sales Data'[Year] ???

[Anonymous]

Thanks for response.

Have converting datetime field into year in SQL like bellow.

YEAR([Order Date]) [Year]

[Pragati11 · Accepted solution]

Hi @Anonymous ,

SAMEPERIODLASTYEAR takes date column as input, but you are just giving it year part of your date.

Check details here:

https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Thanks,

Pragati