Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

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

Greg_Deckler
7 years ago
Well, in DAX you could do this:

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

Icon for Resolver I rank

Resolver I

7 years ago

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)

Anonymous
Not applicable
7 years ago
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
  Resolver I
  7 years ago
  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.
- v-juanli-msft
  Community Support
  7 years ago
  Hi Anonymous
  
  Open Power BI Desktop,click on "New column" from the home menu, "DATE" function should be possible.
  
  Reference:
  
  DAX: DATE
  
  Best Regards
  
  Maggie

Related content