Convert the value of a calculated column to date type

[bchager6]

Hello all,

I am working with the below calculated column which, while intended to represent a date, is actually a text value. I need for it to be a date. I've tried to convert it to a date format by creating a new column, bringing in all of the values from the calculated column, and then changing those values to dates, but it doesn't work. PBI still says it can't convert a text value to date.

Any thoughts?

_Attribute = IF('Table'[Attribute]="FY19","2019",IF('Table'[Attribute]="FY20","2020",IF('Table'[Attribute]="FY21","2021","2022")))

[VasTg · Accepted solution]

@bchager6 

Create the column as below.

Column = 
VAR YR = SWITCH(
    TRUE(),
    'Table'[Column1]="FY19",2019,
    'Table'[Column1]="FY20",2020,
    'Table'[Column1]="FY21",2021,
    2022)
RETURN DATE(YR,01,01)

or don't use "" for values to keep them as whole number and use that in date.

If it helps, mark it as a solution

Kudos are nice too

[bchager6]

Brilliant! Thank you!

[dax]

Hi @bchager6 ,

Could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello!! Try ti change your if result to date. For example date(2020,01,01) . Regards!!

[Anonymous]

uhm...2022 does not look like a number that can be converted to a date.
Why don't you use the DATE function?

https://docs.microsoft.com/en-us/dax/date-function-dax

[Tad17]

What kind of datasource are you working with and can you provide a screenshot of a portion of your data or a sample? You can usually use the FORMAT function to correct issues like this. https://docs.microsoft.com/en-us/dax/format-function-dax