cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sallee_bee Frequent Visitor
Frequent Visitor

How do I create a date field using a text field that has year and month only.

I have a text field that contains year and month only (ex '201801'). How do i create a date field using this text field.

 

Thanks!

Sallee

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How do I create a date field using a text field that has year and month only.

Well, in DAX you could do this:

 

Column = DATE(INT(LEFT([Column1],4)),INT(RIGHT([Column1],2)),1)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


5 REPLIES 5
bogomda Regular Visitor
Regular Visitor

Re: How do I create a date field using a text field that has year and month only.

Sallee,

 

create a new column in same table with following code where varible _Date  can be equal to other column in a table with dates.

 

3rd argument of the DATE function defines a day. In my case the result will be 1/1/2018

 

=
VAR _Date = "201801"
RETURN
DATE(left(_Date,4),MID(_Date,5,2),1)

 

Super User
Super User

Re: How do I create a date field using a text field that has year and month only.

Well, in DAX you could do this:

 

Column = DATE(INT(LEFT([Column1],4)),INT(RIGHT([Column1],2)),1)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


sallee_bee Frequent Visitor
Frequent Visitor

Re: How do I create a date field using a text field that has year and month only.

Hi, I have this formula

Businessdate = DATE(LEFT([BUS_YEAR_MTH],4),MID([BUS_YEAR_MTH],5,2),1)

 

but it is giving me this error:

Expression.Error: The name 'DATE' wasn't recognized. Make sure it's spelled correctly.

 

Thanks for your help!

Sallee

bogomda Regular Visitor
Regular Visitor

Re: How do I create a date field using a text field that has year and month only.

works fine for me. try to use this sample as per link below.

 

https://1drv.ms/x/s!AhUWZ84uo7UAgmM8kvkwm1THdmsj

 

Alternative solution, Greg offered, should also work fine, use either one.

Community Support Team
Community Support Team

Re: How do I create a date field using a text field that has year and month only.

Hi @sallee_bee 

Open Power BI Desktop,click on "New column" from the home menu, "DATE" function should be possible.

Reference:

DAX: DATE

 

Best Regards

Maggie