Forum Discussion
Error with SAMEPERIODLASTYEAR: an invalid numeric representation of a date value was encountered
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:
Based on my test, this can be caused by an invalid datetime value that exceeds the year 9999.
8 Replies
Seems like it does not like your [Date] column. Is it flagged as a numeric field in the data model?
I checked that. It is a date/time field.
Hmm, reading the error more closely, it says MdxScript, is this a Live data source? (DirectQuery)
That function is not supported in DirectQuery mode.
Based on my test, this can be caused by an invalid datetime value that exceeds the year 9999.
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.
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.
