Upper case in the Calender Weekday name

[JayZee70]

Hello!

I have a small issue with my Weekday Name column.

I want the first letter to be an upper case.

What do I do?

This could perhaps be some issue in Microsft settings, but does anyone know how to fix it?

I am using this code:

BR/ Jesper

[Bibiano_Geraldo · Accepted solution]

Hi @JayZee70 ,

Please try to update your DAX to look something like this:

Weekday Name = UPPER(LEFT(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD"), 1)) & LOWER(MID(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD"), 2, LEN(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD")) - 1))

[Bibiano_Geraldo · Accepted solution]

Hi @JayZee70 ,

Understood, you can do this:

Month Name = UPPER(LEFT(FORMAT('Calendar'[Date], "MMMM"), 1)) & LOWER(MID(FORMAT('Calendar'[Date], "MMMM"), 2, LEN(FORMAT('Calendar'[Date], "MMMM")) - 1))

[Sergii24]

Hi @JayZee70, I believe you're right, it seems to be somehow related to your PC settings. In my case, this simple formula provides a desired result:

However, once you use Format() fucntion your value becomes a text, not date anymore. Therefore you can use any text transormations to obrain the necessary result:

Here is a code for copy-paste:

Week Day Capitalize First Letter = 
VAR _WeekDay = FORMAT( WEEKDAY( 'Table'[Date], 1 ), "DDDD" )            //obtain the day of week
RETURN
    UPPER( LEFT( _WeekDay, 1 ) )                                        //get the first letter from a variable and capitalize it
    &                                                                   //concatentate with
    RIGHT( _WeekDay, LEN( _WeekDay ) - 1 )                              //the rest of the word, which is length of a word without a first letter

Good luck! 🙂

[Bibiano_Geraldo · Accepted solution]

Hi @JayZee70 ,

Please try to update your DAX to look something like this:

Weekday Name = UPPER(LEFT(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD"), 1)) & LOWER(MID(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD"), 2, LEN(FORMAT(WEEKDAY('Calendar'[Date], 1), "DDDD")) - 1))

[JayZee70]

Hey!

Thanks, it worked out fine!

How do you code it if you want to do the same thing to Months?

I have a column "Month" but that is simply in number, I want the months by name.

BR / Jesper

[Bibiano_Geraldo]

Can you share a column containig data and dax used to create that column?

[JayZee70]

[Bibiano_Geraldo]

Hi @JayZee70 ,
to get Month name you can use the following DAX:

Month Name = FORMAT('Calendar'[Date],"Mmmm")

This will result in something like this:

[JayZee70]

Yeah, that is about what I have done, I did create a new column.

But I need the names to start with a capital letter.

Like you helped me with the weekdays.

[Bibiano_Geraldo · Accepted solution]

Hi @JayZee70 ,

Understood, you can do this:

Month Name = UPPER(LEFT(FORMAT('Calendar'[Date], "MMMM"), 1)) & LOWER(MID(FORMAT('Calendar'[Date], "MMMM"), 2, LEN(FORMAT('Calendar'[Date], "MMMM")) - 1))

[JayZee70]

Thank you Geraldo!

It works great.

[Bibiano_Geraldo]

You're welcome

[JayZee70]

...now I am just having touble sort the axel in the chart so that it will show Jan-Dec.

Now it sorts in alphabetical order....

Of course, if I use my calender "Month" or my created Month it sorts correctly via the 3 dots in the upper right corner of the chart.

Any idea how to fix this?

[Bibiano_Geraldo · Accepted solution]

Hi @JayZee70 ,

You got month name and month number column in your calendar like this:

Now select the month name column and go to Sort by Column and choose by Month No:

[JayZee70]

Thanks again. Sometimes the easiest things can be hard to see.

[rajendraongole1]

Hi @JayZee70 - you can achieve this at Power Query:

Open Power Query by selecting your data and clicking Data > Get & Transform Data > From Table/Range.
In Power Query, select the "Weekday Name" column.
Go to the Transform tab and click Format > Capitalize Each Word.
Close and load the data back into Excel.

HOpe this helps.

[JayZee70]

If I go to Transform data, I can not edit my Calender, it is not even there, only my other tables.

[Uzi2019]

Hi @JayZee70 

try this 

In the Query Editor, go to the Transform tab, select Format, and select Capitalize Each Word.

I hope I answered your question!

[Uzi2019]

hi @JayZee70 

Check the steps with screen shot

select your column

go to transform tab

select format option

select capitalize each word

I hope I answered your question!