cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

Well, in DAX you could do this:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

Well, in DAX you could do this:

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper II

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

Hi, I have this formula

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

Community Support

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

Helper II

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors