When using CALENDARAUTO() in a new table, for Calendar, I get dates from 1900 approx.

[Syndicate_Admin]

Hello friends, I have several tables, of which 3 have dates from 01-01-2020 to the present. But when generating the calendar table, using CALENDARAUTO(), I get very old dates.

What code do I recommend or if I should observe some relationship between the tables that allows me to recognize the dates of those columns. I worry about letting in PoweQuery that the format is date in those columns.

Thank you in advance for your suggestions.

Best regards

Jorge

[Anonymous · Accepted solution]

Hi  @Syndicate_Admin ,

CALENDARAUTO() is intended to generate the dates by taking the minimum date and maximum date from your model as date range (Which is your expected output as well). Make sure your date fields have proper values and types.

The date fields that you want to consider in CALENDARAUTO() function needs to be of date/datetime datatype. Also it will not consider any calculated fields or tables.

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/Created-Date-table-with-DAX-but-Dates-are-from-1899/m-p/650...

https://community.powerbi.com/t5/Desktop/Date-Dimension-Table-with-Default-1-1-1900-date-Must-entire...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stumit]

You completely missed the point. Run CalendarAuto on any AdventureWorks database where you have imported the DimCustomer or DimEmployee and CalendarAuto will create 90 years of dates. CalendarAuto is a no-no as far as I am concerned.

[stumit]

Solution to what?

My post was pointing out that you need to be careful about which columns are considered dates. Birthdates for customers and employees of, for instance, a 10 year old business should not create date tables that are 70+ years. Read my post in detail again.

[Anonymous]

You can use CALENDAR(DATE(2019,1,1),NOW())

[stumit]

SOLVED!!!!!

CALENDARAUTO is doing what it should. And this may have repercussions for any new business using Power BI, especially those keeping birthdates of the elderly.

In all iterations of AdventureWorks (OLTP and DW, 2008R2 to 2019), DimCustomer[Birthdate] starts in 191[6-7] and DimEmployee[BirthDate] starts in 1939. Details below.

Change the type of each to text.

Nulls in Date/DateTime fields do not affect CALENDARAUTO. Example: DimEmployee[EndDate].

AdventureWorks Dates Affecting CALENDARAUTO()

DBVersion min(Employee[BirthDate])
2008R2       1945-11-17
2008R2DW 1934-01-11
2012           1951-10-17
2012DW     1939-07-11
2014           1951-10-17
2014DW     1939-07-11
2019           1951-10-17
2019DW     1939-07-11

2020DW     1939-07-11

DBVersion min(Customer[BirthDate])
2008R2DW 1914-08-13
2012DW     1916-02-10
2014DW     1916-02-10
2019DW     1916-02-10
2020DW     1916-02-10

Have fun 🙂

[Syndicate_Admin]

The same thing happened to me when some of the columns of dates have empty fields, in those cases I have used CALENDAR and not CALENDARAUTO.

As an example I put as of January 1, 2019:

CALENDAR ( DATE ( 2019; 1; 1 ); DATE ( YEAR ( TODAY () ) + 1; 12; 31 ) )