Forum Discussion

caito103's avatar
caito103
Icon for Helper I rankHelper I
9 years ago
Solved

Transform a string into date (YYYY-MM) and order it properly

Hello my friends,

I searched for solutions for my problem, but this is a bit different as i saw here, hope you can help me. Here i go:

 

I have a column called "PeriodoLibro" where i have Year and Month all together in a string as the img below:

 

I made two new columns (Year and Month) using the following DAX:

 

YEAR = LEFT(Hoja2[PeriodoLibro];4)

MONTH = RIGHT(Hoja2[PeriodoLibro];2)

 

Result:

Then, I need to transform the month into Name, and searching differents alternatives, i tried with:

 

MonthName = SWITCH(RIGHT(Hoja2[PeriodoLibro];2);"01";"Enero";"02";"Febrero";"03";"Marzo";"04";"Abril";"05";"Mayo";"06";"Junio";"07";"Julio";"08";"Agosto";"09";"Septiembre";"10";"Octubre";"11";"Noviembre";"12";"Diciembre")

 

But when i put it in a slicer, is not ordered properly:

 

I tried differents ways, but i cant get it. Also, i dont have a full date in my original query (just as is shown in the first img).

I tried also with FORMAT "MMMM" , but i get an error (cant transform "05" into a month).

 

Any advice about?

 

Thank you so much!

 

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi caito103,

     

    Format your Year and Month column into numbers then add the following column:

     

    Month_Name = FORMAT(DATE(Table3[YEAR],Table3[MONTH],1),"mmmm")

    If you just add the formula below you will get a column with a date and then you can format as your want in your visuals.

    DATE(Table3[YEAR],Table3[MONTH],1)

     

    The result should be the one below:

     

     

    Regards,

    MFelix

     

     

6 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    9 years ago

    Hi caito103,

     

    Format your Year and Month column into numbers then add the following column:

     

    Month_Name = FORMAT(DATE(Table3[YEAR],Table3[MONTH],1),"mmmm")

    If you just add the formula below you will get a column with a date and then you can format as your want in your visuals.

    DATE(Table3[YEAR],Table3[MONTH],1)

     

    The result should be the one below:

     

     

    Regards,

    MFelix

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Set your Sort By for you month name field to be your "Mes" field.

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

    One other possible solution is to create a new column of just '01' and merge it to your existing yyyymm column without any space or dashes.  The resulting merged column can be natively formated into a 'Date' format in Power BI, and then everything automatically sorts correctly. - FOrrest

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi caito103,

     

    Have you tried the solutions provided above? Do they work in your scenario? If the solution works could you accept it as answer to close this thread?

     

    If you still have any question on this issue, feel free to post here. :smileyhappy:

     

    Regards

    • caito103's avatar
      caito103
      Icon for Helper I rankHelper I
      9 years ago

      Yes, it works! Its already marked as solution, thanks for all to everyone, ever giving a hand  :)

       

      Best,