Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
@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?
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.
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'
User | Count |
---|---|
139 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |