Forum Discussion

cdawidow's avatar
cdawidow
Helper III
6 years ago

Format numeric to date YYMMDD

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? 

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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.

    • cdawidow's avatar
      cdawidow
      Helper III
      6 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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.