The automated date table creates dates from 1899 (negative numbers) to 2077

[gtutusaus]

I'm absolutely lost. I have found nothing like this problem in internet. 

I created 4 queries (get&transform) to upload data from some folders. The ETL processes and the schema are perfectly connected and running. I even added steps to change the locale in each column to es-CO.

After I had everything checked, I created 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.

So, then I tried with getting the int from those dates. They were negative.

I connected the date table to the data model but it didn't work. What could be happening?

Thanks!
Gerónimo

[Phil_Seamark]

Hi @gtutusaus

How are you creating your date table?  There a quite a few ways these helpful tables can be build and knowing a little about how you've built yours may help use diagnose.

[gtutusaus]

Thanks! Just with the automated option in PowerPivot

[Phil_Seamark]

In Excel?

[gtutusaus]

yes, in excel...

Do you know by the way to replace dates with null if they are before 1900? I am now trying this approach... this might be the problem for some birthdates

[MarcelBeug]

This topic is now ongoing on (at least) 3 forums, that's exactly the reaon why cross posting is not appreciated as people try to help at one place, while the solution may already be found at anoter place.

So @gtutusaus if you post your topic on multiple forums, please keep each post updated with all information from all forums.

Or better: please mark 1 as the leading topic and indicate in the others that further information should be added to the leading topic to which you will provide a link.

The other locations of this topic are:

Technet

MrExcel Power BI

[gtutusaus · Accepted solution]

Thanks, MarcelBeug.

Didn't know here about a rule for cross-posting in this forum. I'm being adding the links everywhere I've seen it is required and I always keep updated every post I create. You can see that everywhere.

I think that some momentos ago, I kind of understood what was happening. It was hard to see as there were at least 2 issues mixed:

• Firstly, some birthdates are wrong. I could not get any help in internet on how to replace in PowerQuery just years so I decided to convert the field into text.
• Secondly, I didn't know that the date table doesn't work with time (hours and minutes) so it wasn't matching the PK to any field in the data model. I decided then to transform every date/time format into just date format.

Thanks, everybody for your support and patience. This issues were driving me crazy. Unfortunately, I didn't get all the answers but at least I came up with something that works for me.

Best,

Gerónimo

[Sean]

The first officially supported date by DAX is March 1, 1900