cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chookz Frequent Visitor
Frequent Visitor

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 

3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

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

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.
chookz Frequent Visitor
Frequent Visitor

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

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?

Highlighted
SEliza86 Frequent Visitor
Frequent Visitor

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

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