cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
caito103 Regular Visitor
Regular Visitor

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:

MtxXf9L7Q5WMXC3PQtFarQ

 

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

 

YEAR = LEFT(Hoja2[PeriodoLibro];4)

MONTH = RIGHT(Hoja2[PeriodoLibro];2)

 

Result:

0H6VWYVsSD_mQq1YCusSnw

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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:

 

month_name.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
Super User
Super User

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

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

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

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:

 

month_name.png

 

Regards,

MFelix

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fhill Established Member
Established Member

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

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 Super Contributor
Super Contributor

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

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. Smiley Happy

 

Regards

caito103 Regular Visitor
Regular Visitor

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

Thank you so much! Thats worked very good.

caito103 Regular Visitor
Regular Visitor

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

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

 

Best,