Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
caito103
Helper I
Helper I

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
MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
v-ljerr-msft
Employee
Employee

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

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

 

Best,

fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




MFelix
Super User
Super User

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

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thank you so much! Thats worked very good.

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.