Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
markmess77
Resolver I
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
Fowmy
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 🙂

YouTube  LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

Hi , @markmess77 

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

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

52.png

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.

 

negi007
Community Champion
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)

 

date.PNG




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



Proud to be a Super User!


Follow me on linkedin

Fowmy
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 🙂

YouTube  LinkedIn

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Greg_Deckler
Super User
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.


Follow on LinkedIn
@ 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...
amitchandak
Super User
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.