cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## Way to convert integer to datetime with DAX?

I'm looking to convert my DateKey to a datetime datatype for a calculation.

My DateKey is in yyyymmdd format and is stored as an integer. So for example, if I had a value of 20200701, I would want it converted to a datetime value of July 1, 2020 in the context of my calculation.

I've been reading through the documentation, but have not been able to find any DAX functions that are able to do this.

Any tips?

1 ACCEPTED SOLUTION
Super User

@markmess77

New Column = CONVERT(COMBINEVALUES("/",LEFT([DATE KEY],4),MID([DATE KEY],5,2),RIGHT([DATE KEY],2)),DATETIME)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
5 REPLIES 5
Community Support

Hi , @markmess77

You also can try to create a  custom column in Power Query Editor.

``=Date.From(Text.From([Column1], "en-US"))``

Please refer to the sample file for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Champion

@markmess77  You can split 20200701 into three column like Year : 2020, Month: 07 and Day: 01. Then you can simply use date function to create date like below

My_date = Date(year, month, day)

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

Proud to be a Super User!

Super User

@markmess77

New Column = CONVERT(COMBINEVALUES("/",LEFT([DATE KEY],4),MID([DATE KEY],5,2),RIGHT([DATE KEY],2)),DATETIME)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

Did I answer your question? Mark my post as a solution! and hit thumbs up
Super User

@markmess77 Power Query will convert that automatically. Just right-click and change type to Date. If you need your original and this, just duplicate the column in Power Query, change it to Date and leave your original. Then you have both.

@ 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!:
The Definitive Guide to Power Query (M)

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

@markmess77 , You can create a date like

Date = date(left([datekey],4) mid([datekey],5,2) ,right([datekey],2))

Then you can use format from column Tools. or format function

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors