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 was playing around with the DAX function CONVERT and was specifically trying to change an integer value to a datetime value.
So for example I have an integer column with the value of 20200527
I thought this would be easy to just simply write CONVERT(20200527, DATETIME) and I should get my date.
But apparently this gave an error?
I tried to search around and found someone had suggested the following formula
New Column = CONVERT(COMBINEVALUES("/",LEFT([DATE KEY],4),MID([DATE KEY],5,2),RIGHT([DATE KEY],2)),DATETIME)
Which works, but its just tedious to have to write out all the extra LEFT, MID, RIGHT etc.
Looking at this link: https://dax.guide/convert/
I see the following works just fine
Solved! Go to Solution.
Hi @rodneyc8063
If you directly enter a numeric value in the position of the expression, then the returned result is based on 1899/12/30 0:00:00 plus the numeric value .
For example :
Because Power BI doesn't know how to divide the numeric value into year, month, day, hour, minute and second .So the way you said to plug a straight integer field in without having to parse it is not feasible .
If you want to convert integer to date/time format , through Power Query or other dax .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @rodneyc8063
If you directly enter a numeric value in the position of the expression, then the returned result is based on 1899/12/30 0:00:00 plus the numeric value .
For example :
Because Power BI doesn't know how to divide the numeric value into year, month, day, hour, minute and second .So the way you said to plug a straight integer field in without having to parse it is not feasible .
If you want to convert integer to date/time format , through Power Query or other dax .
Best Regards
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@rodneyc8063 , Try like
a new column =date(Quotient([Number], 10000) , Quotient(mod([Number], 10000),100), mod(mod([Number], 10000),100))
Thanks @amitchandak for the suggestion
I believe this should work as well, I just wish with the CONVERT function, we can just plug a straight integer field in without having to parse it, or put in sub functions. Was just curious if there was another (even lazier) way to convert 😛
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 |
---|---|
112 | |
97 | |
83 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |