Way to convert integer to datetime with DAX?

[markmess77]

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? 

[v-easonf-msft]

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.

[negi007]

@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)

[Fowmy · Accepted solution]

@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

[Greg_Deckler]

@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.

[amitchandak]

@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