Not a legal OleAut Date

[keno]

I'm getting this error when a schduled refresh runs (it doesn't error when manually refreshing). I've found the reason why this happens in this forum, e.g. ANSWERED QUESTION.

Data source error:	Not a legal OleAut date.. The exception was raised by the IDataReader interface. Table: Manual Attendance Full.
Cluster URI:	WABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID:	452648da-2252-4845-88de-515b0dd58161
Request ID:	8a1ec65c-a4a4-4c79-bb04-62807acc2ecc
Time:	2019-02-12 07:24:10Z

The question is WHY and HOW is this an error. Dates before 1/1/1900 are more than valid, I don't understand why Power BI would kick them out. And why Power BI would kick them out on a schduled refresh, of all things.

I'm purposefully displaying dates that are not 100% correct as this dashboard is to help us validate incorrect scans of documents.

Does anyone have a work around? I don't want to lose the incorrect data as I need the entire dataset to be present.

Thank you,

Ken.

[Stachu]

dates like this are allowed in M/Query Editor (page 35)
https://docs.microsoft.com/en-gb/powerquery-m/power-query-m-language-specification

but not in DAX
https://docs.microsoft.com/en-us/dax/date-function-dax

there are also some examples there with dates before 1899, but it doesn't look very promising

if you have such a wide year range do you use months/days at all? maybe it will be easier to just treat date as a numeric dimension rather than date

[keno]

The dates need to be readable by humans, so the date format of dd/mm/yyyy is the best one to use. 

This is for correcting errors. I don't really understand how they could build a system that only looks at dates after 1/1/1900.

I guess the real question is, why do they allow it for manual refreshes, but not scheduled refreshed?

[v-yuezhe-msft]

@keno,

A worksaround I can think of is to change the data type of the date field to text or use manual refresh instead of schedule refresh.

Regards,
Lydia