Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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.
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) )
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
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
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
@OwenAuger
Hi Owen,
Any idea why I got an error
Hi @Pbiuserr
At a glance two things:
Regards,
Owen
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!
Hello,
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
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
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
Thank you! I now have this working, thanks to your help and insight!
Casey
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |