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