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
LandonDodge
Employee
Employee

Single letter month abbreviations

There are many posts about three-letter month abbreviations but none about one-letter month abbreviations (J F M A M J J A S O N D).  I would like to create a chart with one-letter month abbreviations on the x-axis and have it sort in chronological (not alphabetical) order across multiple years, and yet I have been unable to find anything online about doing this, which surprises me because I would think this would be a commonplace occurrence.  I can do it in Excel with ease in just a few clicks.  How can I do it in Power BI desktop, including using DAX code to get there?  I would appreciate any help from the community.  Thank you.

1 ACCEPTED SOLUTION

You're welcome - glad this is at least part way to a solution.

 

Could you post an image of an Excel version of the chart you are trying to produce? Some sample data may also be useful.

 

The exact solution may depend on the chart type and how you want your data to be aggregated.

 

If you need the one-letter abbreviations to span multiple years in some way, or correspond to dates rather than months, we could come up with a way of encoding that information, possibly using a few different invisible characters.

 

By the way, I updated my uploaded file with a simple line chart visual over two years. This may be nothing like what you are wanting to create, but just put it there as an example.

 

Regards,

Owen

image.png

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

18 REPLIES 18
michellepace
Resolver III
Resolver III

Here is Owen's solution but in Power Query (I got the original date table from here https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390)

 

New Source > Blank Query > Advanced Editor > (paste everything below)

 

 

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonth ) then FYStartMonth else 1,
    WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
    TableFromList = Table.FromList(AddToday, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
    InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type number),
    InsertMonth_sort = Table.AddColumn(InsertYear, "Month_sort", each Date.Month([Date]), type number),
    InsertDay = Table.AddColumn(InsertMonth_sort, "DayOfMonth", each Date.Day([Date]), type number),
    InsertMonth = Table.AddColumn(InsertDay, "Month", each Date.ToText([Date], "MMMM"), type text),
    InsertMonthShort = Table.AddColumn(InsertMonth, "MonthShort", each Date.ToText([Date], "MMM"), type text),

    // Owen's solution here: 
    //(just sort this column by "Month_sort" and it will work!)
    InsertMonthInitial = Table.AddColumn(InsertMonthShort, "MonthInitial", each Text.Start([MonthShort],1) & Text.Repeat(Character.FromNumber(8203), [Month_sort]) , type text)

in
  InsertMonthInitial
in
fnDateTable

 

 

michellepace
Resolver III
Resolver III

Hello @OwenAuger , thank you for this great solution. It is not 100% relevant to the power bi forum, however I thought I'd give it a try:

 

I'm using T-SQL, do you perhaps know how I can generate the (Unicode character 8203) ?

@michellepace I'm certainly not an expert on this but the NCHAR function seems to be the one you want in recent versions of T-SQL.

i.e. NCHAR(8203) for that particular character.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you very much Owen. If anyone else needs it, here is the equivalent to Owen's solution but in T-SQL:

 

MonthInitial = LEFT([Month],1) + replicate(NCHAR(8203), month(date) )
OwenAuger
Super User
Super User

@LandonDodge

 

This is an interesting one 🙂

 

Here's an example of what I came up with.

 

Because of the duplicate initial letters, one method is to create a calculated column (either DAX or Power Query) that includes the desired initial letter of the month, followed by zero-width blank characters (such as Unicode character 8203), repeated a different number of times for each month. I used the month number (1-12) as the number of repetitions.

 

In DAX, I did this with a calculated column:

Month Initial = 
LEFT( FORMAT( 'Calendar'[Date], "mmm" ), 1) & REPT( UNICHAR ( 8203 ), MONTH( 'Calendar'[Date] ) )

I also added a Month Number to the 'Calendar' table, and set Month Initial to Sort By Month Number.

 

This might need to be tweaked depending how you want it displayed in the visual.

 

You could also do this with the year included in the column as well.

 

Regards,

Owen

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you, OwenAuger.  I admire your intellect for coming up with that approach, which would not have occurred to me.  It gets me a good part of the way there, but the challenge I'm now having is that I can't use the one-letter abbreviations on the x-axis of a chart, since PBI Desktop still treats them like categories to be summed instead of as a time series.  I tried sorting the new Month Initial column by the Date column, but it wouldn't work owing to the duplicates in the Month Initial column (which would also be the case if three-letter month abbreviations were used over more than one year, as I've found separately).  Ultimately my need for the one-month letter abbreviations is so that they can be used on several very small charts (hence the need for such abbreviated abbreviations).  Any ideas about how to address the charting issue?  And thank you again for your reply!!

Thanks Owen, this is very clever, thank you for the idea.    FYI for others,  I added a month index onto my date table,  which has a start date variable (M code  (([Year] - Date.Year(StartDate)) * 12) +
([MonthNum] - Date.Month(StartDate)) + 1)   then added a calculated column in the table as per Owen,   (Dax 

Month Initial =
LEFT( FORMAT( 'Calendar - Trial Start Date'[Date], "mmm" ), 1)
&
REPT(
UNICHAR ( 8203 ),
CALCULATE(MAX('Calendar - Start Date'[Month Index])
) ) and then sorted the calculated column by the month index.  Worked great.   


SteveCnz_0-1633423886897.png

 

You're welcome - glad this is at least part way to a solution.

 

Could you post an image of an Excel version of the chart you are trying to produce? Some sample data may also be useful.

 

The exact solution may depend on the chart type and how you want your data to be aggregated.

 

If you need the one-letter abbreviations to span multiple years in some way, or correspond to dates rather than months, we could come up with a way of encoding that information, possibly using a few different invisible characters.

 

By the way, I updated my uploaded file with a simple line chart visual over two years. This may be nothing like what you are wanting to create, but just put it there as an example.

 

Regards,

Owen

image.png

 

 

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Problem now is the tooltip does not give the full month name now. But I was able to.

@OwenAuger 
Hi Owen,

Any idea why I got an error 

 

Month Initial =
LEFT( FORMAT( Date[Date], "mmm" ), 1) & REPT( UNICHAR( 8203 ), MONTH( Date[Date] ))
 
A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
 
I just replaced your calendar field with mine

Hi @Pbiuserr 

At a glance two things:

  • Can you confirm you're creating a calculated column, not a measure? I get the same error as you if I try to create a measure with that code.
  • Normally single quotes are required when referencing tables named Date (as it is a function name). Try adding those if you haven't already (may be locale-dependent).

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hello @OwenAuger 
You're completly right! I was after tough day with lots of hours at the desk, therefore I've tried to make a measure. First thing this morning was doing calculated column and it works brilliantly! Thank you for your input in the community!

Anonymous
Not applicable

Hello,

@OwenAuger 

The example solution and image you have in this post are exactly what we looking for, but I can't seem to find the file that you reference. Can you possibly display or send me the example file solution? 

 

Best,

Grant

@Anonymous 

Here is the dropbox link for the file I created:

https://www.dropbox.com/s/hn1mm622b9fedq5/Month%20initial.pbix?dl=0

 

I've also attached it to this post for posterity 🙂

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

OwenAuger, thank you once more.  Your chart is indeed what I am trying to get to, albeit in a more compact form.  My results are improving as they are now in chronological order; now if I can just get Power BI Desktop to stop adding both Januarys, both Februarys, etc., (I have two years's worth of data) to make it a true time series across the two years, I will be there.  Yours is doing it correctly, so the key must be there in your pbix file.  I have downloaded it and am studying it closely to understand what else I should do to mirror your result.  Thank you!

Hi @LandonDodge,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That's good news - you're welcome.

 

The key points are to put both Year and Month Initial in the Axis well, then click the "Expand All" button on the visual (the rightmost of the three arrow buttons on the top left).

 

Best regards

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Thank you!  I now have this working, thanks to your help and insight!

 

Casey

 

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.