@michellepace Can you please share how you did that in DAX?
1 Like
Hi Antriksh (@AntrikshSharma). It’s funny you ask that. I can’t claim the fame for coming up with this, but when I first saw it - - it was in DAX – but I needed to have it in SQL. Needless to say this now comes in all three flavors because I’ve converted the original Dax to both M-Code (see script above) as well as SQL - - see here for DAX and SQL: https://community.powerbi.com/t5/Desktop/Single-letter-month-abbreviations/m-p/462132.
2 Likes
@michellepace Haha, we all learn everyday, actually for some reason my visuals weren’t grouping and showed duplicate values, entered same code again and again and then it started working, thanks for sharing !
1 Like
Love that invisible characters trick to address the duplicate name issue. Saw Ferrari do that in a video last year, and thought it was one of the coolest DAX tricks I’d ever seen.
Really like the way you’ve applied it to the single letter month names.
- Brian
1 Like
Hi @michellepace,
I’ve got solutions for the MonthInitial and DayOfWeekInitial columns.
I’ll look into the Fiscal Week requirement and get back to you, hopefully later today.
4 Likes
Added the Fiscal Week based on the logic you’ve provided, see this article, it requires a FYStartMonth and FYStartOfWeekDayName . Note that this can create partial weeks around the FY Start and End date. And another thing to note, that in the DAX code there’s a misalignment in FY this is sorted in the M code.
Also added MonthInitial and DayOfWeekInitial columns that use the the M function Character.FromNumber to add a unicode Zero-Width Space character if a letter isn’t distinct.
@BrianJ,
if you want to update the Cheat Sheet for Extended Date Table.
MonthInitial should be sorted by MonthOfYear and
DayOfWeekInitial sorted by DayOfWeek
4 Likes
Thanks – that’s fantastic. Biggest upgrade to the date table in a long time. I will do a major overhaul of the cheat sheet this week, and add the information for how to sort each column. Once done, I will post it and at that point I think doing a video to highlight both would be great.
- Brian
1 Like
I think there may be a little mistake in the iso weeks. Sorry, I don’t mean to dig for mistakes. It’s the first time “iso weeks” has ever been important to me. I’ve just started doing some work for a company who speaks completely in “weeks like we see on our desk calendars” haha. Which is iso weeks.
I copied Melissa’s latest script into the attached pbix (with no changes at all). When you get a moment could you please have a quick look? Happy to be corrected but the below looks a little off. I added comments in the hope you’ll see a pattern and get a good idea of where the m-code error could be… my m-coding leaves much to be desired (still) 
.
Dates_IsoWeekCheck.pbix (113.4 KB)
Thanks once again for this!
Michelle
1 Like
Hi @michellepace,
Thanks for your feedback, I appreciate it.
However I don’t see an issue with the ISO Weeks, so I suspect you’ve selected the “Year” field instead of the "ISO Year" field and that has gotten you into trouble… 
Only the boundaries of the date range can cause a partial first- and or last week in the Date table. Let me know if that solves it for you.
1 Like
So it is the initial code that keeps getting updated in this thread with the revisions - I couldn’t see an edited flag but looked to be.
On the topic of the Day initials, a stand-alone Short Month Name - eg Jan, Feb is something that i often use. atm, it is concatenated in the Month & Year column.
1 Like
Hi @Cameron,
Yes to prevent you from having to search through a lengthy thread, to locate the latest version, I always update the initial post. So if you always copy the code from there you can take advantage of the latest modifications and/or updates as soon as they’ve become available.
Now also included the MonthShortName
.
@michellepace and @BrianJ,
Just added the sort by column FWeeknYear
3 Likes
Thanks for the heads up. I was just working on the cheat sheet a few minutes ago, and will incorporate the new fields and sort fields tomorrow. Was going to ask Karen if she could work her graphic design magic on the cheat sheet, so that we’ll have a really nice, Enterprise DNA branded version.
- Brian
2 Likes
Hi @michellepace,
The QuarterCompleted is already in the Date table and instead of a YearCompleted you could use the YearOffset <0. Nice to see you’re putting the Date table to good use 
1 Like
Hi @Melissa.
REQUEST: please add “ISO Quarter”
I really love this dates table. It is fantastic. May I ask you to add one last little tweak in that you add ISO Quarter too?
WHY?
I’d like to build a Date hierarchy with ISO year, ISO quarter and ISO week. As your Quarter field is based on the Gregorian calendar, it does not tie up when I use my ISO Year. For instance, looking at ISO year 2020 which started on the 30th of December:
ABOUT ISO Quarters
(just some additional info for everyone to read)
ISO-8601 calendars have a consistent number of weeks in each quarter and a consistent number of days each week, making the ISO-8601 calendar popular when calculating retail and financial dates.
The first three quarters in the ISO-8601 always have 13 weeks in them, with the last Quarter having either 13 or 14 weeks in it, depending upon the start of the next ISO-8601 year.
https://help.tableau.com/current/pro/desktop/en-gb/dates_calendar.htm
Thank you, really this is going to be forever useful.
Michelle
1 Like
I’ve added ISO Quarters in the attached pbix. In the script itself (should you want to add it), here are the lines added:
// ---------------------------------------------------
// ISO QUARTER: for Melissa to review before adding
InsertISOQuarter = Table.AddColumn(InsertWeeknYear, "ISO Quarter", each if [ISO Weeknumber] <= 13 then "Q1" else if [ISO Weeknumber] <= 26 then "Q2" else if [ISO Weeknumber] <= 39 then "Q3" else "Q4", type text),
InsertISOQtrYear = Table.AddColumn(InsertISOQuarter, "ISO Quarter & Year", each [ISO Quarter] & " " & Number.ToText([ISO Year]), type text),
InsertISOQtrnYear = Table.AddColumn(InsertISOQtrYear, "ISOQuarternYear", each [ISO Year] * 10000 + Number.FromText(Text.End([ISO Quarter],1)) * 100, Int64.Type),
// ---------------------------------------------------
.
ABOUT ISO Quarters
(just some additional info for everyone to read)
ISO-8601 calendars have a consistent number of weeks in each quarter and a consistent number of days > each week, making the ISO-8601 calendar popular when calculating retail and financial dates.
The first three quarters in the ISO-8601 always have 13 weeks in them, with the last Quarter having either 13 or 14 weeks in it, depending upon the start of the next ISO-8601 year.
https://help.tableau.com/current/pro/desktop/en-gb/dates_calendar.htm
Cheers, MichelleISO QUARTERS - added.pbix (91.8 KB)
2 Likes
Hi @michellepace,
Good to see you’ve solved that on your own
Thanks for posting your method, it’s similar to my approach… I’m going to review the full Date table M code this weekend, might do some other updates…
All the best.
5 Likes
If making some revisions, please include Fiscal Day of Year , Fiscal Week of Year & Fiscal PYTD. I think those would be great additions. I currently do most of these as calculated columns, but know that Power Query is better suited for this.
Thanks
Jarrett
1 Like
Hello.
Thank you, the table is really powerful.
I copied and pasted the function in Power Query and tried to convert the column “Month & Year”, which is generated automatically, into Date format. It worked fine, except for the month of “March” . I can see that it gives me an error for March only for all the years.
I have a file with Monthly budget data (month and year) and I’m trying to link the DateTable with these dates via TREATAS function. Works fine, but it excludes the month of March.
Can anyone help me on this?
Thank you very much.
Marco
1 Like
Hi @marcoercolani
I would recommend that you start a new posting as this posting is already solved.
thanks
1 Like