text to date converting

[waleed111]

when i write this formula:

sales year = FORMAT('Purchase Order Received Report'[Added Time], "yyyy")

then use it as date type it show me this error:

Cannot convert value '2020' of type Text to type Date.

how to fix this problem?

[v-shex-msft · Accepted solution]

HI @waleed111,

So you mean you want to change the date type field to only display the year values? If this is a case, I'd like to suggest you take a look at the following link about table properties text formatting which only changes the disabled format and keeping raw field values.

Use custom format strings in Power BI Desktop 

Notice: format function will return text values with the custom format as you defined in the optional parameter.
Regards,

Xiaoxin Sheng

[amitchandak]

@waleed111 , what is data in 'Purchase Order Received Report'[Added Time]

This error can come if [Added Time] has only year no date.

Or you are trying to change data type of the column to date

[waleed111]

yes i try to change data type of the column to date

[AllisonKennedy]

@waleed111

The FORMAT function in DAX converts your data to Text. Because you're using "YYYY" you could choose to convert this to a Number data type, but it is no longer a Date. You need more information to create a Date type. Just keep the Year column as Text, and keep the date in a separate column.