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
elpulley
Frequent Visitor

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

 
9 REPLIES 9
AlexChen
Employee
Employee

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! 

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
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.