Error with SAMEPERIODLASTYEAR: an invalid numeric representation of a date value was encountered

[vanessa]

I have created a metric to calculate the sales for last year.

Below is the formula:

LastYear_Sales = CALCULATE(SUM('Fact_Sales'[SALES_AMT]),SAMEPERIODLASTYEAR('Dim_Day'[Date]))

when I create a table with: Date from Dim_Day and LastYear_Sales, I get the following error:

[Stefan82]

I encountered the same issue when working with SAMEPERIODLASTYEAR. It seems like the date table needs to be Marked As Date Table. This did the trick for me.

[Stefan82]

I encountered the same issue when working with SAMEPERIODLASTYEAR. It seems like the date table should be Marked As Date Table. This did the trick for me.

[v-chuncz-msft · Accepted solution]

@vanessa,

Based on my test, this can be caused by an invalid datetime value that exceeds the year 9999.

[Greg_Deckler]

Seems like it does not like your [Date] column. Is it flagged as a numeric field in the data model?

[vanessa]

I checked that. It is a date/time field.

[Greg_Deckler]

Hmm, reading the error more closely, it says MdxScript, is this a Live data source? (DirectQuery)

That function is not supported in DirectQuery mode.

[vanessa]

I am using Import mode to fetch data from MySQL.

[SivaMani]

@vanessa,

Could please share the sample data?

It would be helpful to assist.