Forum Discussion
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?
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 🙂
5 Replies
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
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.
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 🙂
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)
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.
