Date table is throwing negative numbers in PowerPivot

[gtutusaus]

Hi experts,

Hope I'm the right place. I have really looked in many dozens of webpages, forums and manuals but I cannot find anything like this.

Basically, I have 4 tables connected by Power Query in a schema that works perfectly. Data types and relationships are ok. Date columns have been set to my locale (es-CO) mostly, with the format date/time (d/m/yyyy hh:mm:ss) except for birthdays.

I created then in Power Pivot a "Date Table" but even before connecting it to the data model, I realised about two funny things:

• The function for the day number of the week, weekday() is throwing #ERROR: "An argument of the function has the wrong data type or the result is too large or to small."
• The calendar built starts in 1/1/1899 and 2077. 

Because of this, I tried then with these DAX formula:
=DATE(year('Calendar'[Date]);month('Calendar'[Date]);day('Calendar'[Date]))
and dates were converted to "1/1/3799 12:00:00 a.m." 

and 

=int('Calendar'[Date]) and they start with negative numbers (-363) 

What's going on?! I'm completely lost.

Thanks, Gerónimo.

[v-caliao-msft]

@gtutusaus,

What's your expected result? This is Power BI forum, from your screenshot it's seems that you create the calculated column in PowerPivot. If your issue is related to PowerPivot, please post it on the link below.
SQL Server PowerPivot for Excel

Regards,

Charlie Liao              

[MarcelBeug]

This topic is initiated many times on various forums.

@gtutusaus I  guess you overlooked this one to be updated with the solution....

[gtutusaus]

@MarcelBeug,next time i'll be counting on your help...