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

[caito103]

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!

[v-ljerr-msft]

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. 

Regards

[caito103]

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

Best,

[fhill]

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

[MFelix · Accepted solution]

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

[caito103]

Thank you so much! Thats worked very good.

[Greg_Deckler]

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