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
bchager6
Super User
Super User

Convert the value of a calculated column to date type

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")))
1 ACCEPTED SOLUTION
VasTg
Memorable Member
Memorable Member

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

 

image.png

 

If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn

View solution in original post

6 REPLIES 6
VasTg
Memorable Member
Memorable Member

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

 

image.png

 

If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn

Brilliant! Thank you!

dax
Community Support
Community Support

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
Not applicable

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

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
Solution Sage
Solution Sage

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

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.