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

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

 
9 REPLIES 9
AlexChen
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

jmalone
Resolver III
Resolver III

@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?

elpulley
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??

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

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

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

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! 

Jurgita
Frequent Visitor

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. 

 

 

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'

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.