Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chookz
New Member

30/12/1899 time (duration) column affecting CALENDARAUTO

Hello all.

I am trying to create a date date for my model using New Table > CALENDARAUTO().

However, this returns a start date of 1/1/1988!

My data source has a time (duration) column from excel formatted as h:mm:ss.

Changing this data type to Date/Time, it has date 30/12/1899 attached to it. I assume this is what CALENDARAUTO is then starting my date table at 1/1/1899.

I've tried converting the column to Time > format as hh:mm:ss. The date 30/12/1899 doesn't show, doesn't show in the table but still comes up in the new table using CLAENDARAUTO function.

I've also tried changing the data type to decimal number, however CALENDARAUTO still returns a start date of 1/1/1899

Now I don't know what to do!

Can anyone assist me in importing a duration time correctly, or removing the date from my time column?

Thanks in advance 

8 REPLIES 8
wOOLRITCH
Frequent Visitor

Hi i also wonder if there is a solution to this... i have the same problem as well.

Any solution?

 

I have a time column in my data. Even though it's formated at time, I can only assume this is causing my Calendarauto() to result in dates starting in 1899. I want my calendar to adjust based on my data and don't want to hard fix my start and end dates. So it would be great to know if there is a fix. 

FrankAT
Community Champion
Community Champion

Hi @Drewz,

[1] I have a data table with a column of dates. Some dates aren't unique.

11-07-_2020_00-27-08.png

 

[2] Use the function CALENDAR(), MIN() and MAX() to get a contiguous set of dates. If the date column in the data table changes it will be reflected in the calculated table (see figure [2])

 

11-07-_2020_00-28-11.png

 

[3] To use the DAX function CALENDARAUTO() can be a little bit tricky. Quote: "CALENDARAUTO() returns a table with a single column named "Date" that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model." But which does it use if you have multiple date columns in your model? I think it's good practice to built a calendartable by yourself.

 

11-07-_2020_00-31-54.png

 

Regards FrankAT

Hi @FrankAT 

 

I was having the same issue as @Drewz . This solution worked for me! 

 

Thanks! 

v-qiuyu-msft
Community Support
Community Support

Hi @chookz,

 

CALENDARAUTO() returns a contiguous set of dates automatically based on data in the model. The rule to return date column is, for example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.

 

CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.

CALENDARAUTO(3) will return all dates between March 1, 2010 and February 28, 2012.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @chookz

 

When using CALENDARAUTO(), where does date information come from? Seems like the date data is being misread. How could we check that? 

Hi @v-qiuyu-msft

Thanks for your reply. I understand how CALENDARAUTO works, my issue is that when I have a duration value in my table, it is returning the mindate as 1/1/1899 (as duration values seem to have a date of 30/12/1899 attached to them). 

I guess what I'm asking is, what is the 'best practice' when entering duration values in Power BI in  order to avoid an underlying date scrwwing up your model?

Did you ever find a solution to this? I am having the same problem.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.