Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.
@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)
Proud to be a 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 , 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |