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.
Hi guys, I was wondering how best to convert my dates from their numeric value to date? Currently I get numbers set up like so
YY-MM-DD example, 200217.
Any easy way to do this in dax or power query?
@cdawidow , you can set format under - https://docs.microsoft.com/en-us/power-bi/desktop-custom-format-strings
or under Column/measure tool you have option
or create a column
New column = format([Date],"YY-MM-DD")
Hey, your solutions do work, but for the year , I am still getting a wrong vlaue. For example, 200702 should give me 2020-07-02 but instead I get 2449-07-02 any ideas why?
Run this in DAX Studio.
EVALUATE
{
var __dateAsNum = 200702
return
date(
int( __dateAsNum / 10000 ) * 100 + 20,
mod( int( __dateAsNum / 100 ), 100 ),
mod( __dateAsNum, 100 )
)
}
It returns 2020-07-02 as a date.
@cdawidow - You can use the FORMAT command. You can also change the format in the ribbon under Measure Tools or Column Tools. There is also display formatting as discussed in Chelsie Eiden's Duration - https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
Could also use SUBSTITUTE to replace "-" with "".
Those are all DAX solutions. I am sure there are Power Query ones too. For example, you could split that column on "-" and then concatenate it back together the way you want. Or, heck, just replace your "-" with "" in Power Query editor.
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |