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

How do I tell PowerBI that my year column is a date in "YYYY" format?

 
9 REPLIES 9
Highlighted
Frequent Visitor

I have a year column that is currently formatted as a "Whole Number" data type. When I try to convert to date, PowerBI thinks that this is the code for the date and changes the year. When I change the format for the date to "YYYY" it still thinks that these are values for 1905. I feel like it should be relatively simple to tell PowerBI that these are years. What am I doing wrong??

Highlighted

YYYY date issue pic 1.jpgYYYY date issue pic 2.jpgYYYY date issue pic 3.JPG

Highlighted

A year is a whole number.  As there is no month or day value in the field, then you don't want to or at least at this level of visual - you don't need to - change the field type to date.

 

 

www.CahabaData.com
Highlighted
Resolver II
Resolver II

@CahabaData is correct - a Year by itself is not a Date. A year can be a Number or Text data type, but it is not a Date without a month and day.

 

There is really no reason to convert a year to a Date field anyways. You can use the field as a filter, in a visual, or in a measure without it being a Date type.

 

Is there something you are trying to accomplish that you cannot currently do?

Highlighted
Microsoft
Microsoft

Hi,

 

In Power BI, once you change the data type into date, it will format your data into a date.

 

It’s impossible to tell powerbi that a year column is a date in “yyyy” format. The year part of a date can only be in “Whole Number” type.

 

Best Regards
Alex

Highlighted

I have the same problem. I need the text that is years, eg. 2013 to trasnform into date, ecause I want to use compare years function. and this function takes argument as a date only. But if I ask power BI to trasfer it into the date, it gives me 1905 year! 

Highlighted

I did solve this problem yesterday just by creating a new column, telling to take text date from first colum, than added, 1 as a  month and 1 as a day, and in the new column it transformed me a nice date:

Survey date = date([Survey year]; 1;1)  

 

Survey year was just a text "2016" for example. And calculated  "Survey date" I just changed format as date, and chose Format as YYYY. 

 

 

Highlighted

When I try to add a new column as mentioned: "=date(TableName[Year]; 1; 1)", I get the following:

'Expression.Error: The name date is not recognised'

Highlighted

However, if I don't change the column type to date, even if I change the visual by order by year, the visual still does not show in chronological order

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors