We've all been there. You go to create your visual with Months on the axis and you see April, August, December … and realize you forgot to create Sort By columns. One way around that is to use a "Starter File" approach described in a previous post, but this article describes two approaches to avoid it in new Date tables. Also provided are full Date tables in both DAX and M you can use/adapt as needed. Along with saving time, not having Sort By columns also simplifies your DAX as you no longer need to include a Sort By Column when you remove filters. Check out the video and/or read the article below.
Approach #1 – Custom Format Strings
The first approach is to use custom format strings. These are an extension of the approach described on SQLBI. In a typical Date table, FORMAT( [Date], "MMMM" ) will return "January", "February", etc. As a result, all of the January rows (regardless of Year) will have the same value. With custom format strings, you need to create a Date value that is also the same for all of the rows of interest, and then you simply format that Date as text. Because they are really date values behind the scenes, they automatically sort in the desired order. Below is a Date table that creates additional columns with constant Date values in the rows of interest. The syntax used to create the Date table is adapted from this SQLBI article.
Date_NoSort_CustomFormatStrings =
VAR BaseCalendar =
CALENDAR( DATE( 2020, 1, 1 ), DATE( 2021, 12, 31 ) ) //or use MIN() and MAX() with Date column(s) to set range dynamically
RETURN
GENERATE(
BaseCalendar,
VAR basedate = [Date]
VAR yearnum =
YEAR( [Date] )
VAR yearmonth =
EOMONTH( [Date], 0 )
VAR monthname =
DATE( 2021, MONTH( [Date] ), 1 )
VAR dayname =
WEEKDAY( [Date] ) + DATE( 2021, 1, 2 )
RETURN
ROW(
"Year", yearnum,
"YearMonth", yearmonth,
"Month", monthname,
"DayName", dayname
)
)
To get all the rows in each month to have the same Date value, the EOMONTH() function is used in the yearmonth variable. For monthname, the month number only is used in a hard-coded DATE() (the year and day don't matter). For dayname, the WEEKDAY() number is added to any DATE() that is a Saturday (the WEEKDAY() of any Sunday is 1 and all Sundays will be converted to a common Sunday date value, and same for Monday, Tuesday, …).
Once you have the new Date columns set up, you need to apply the Custom Format Strings. In the Diagram View, simply select the column, confirm the type is set to Date/Time and choose Custom for the format. Shown is the one for the "Month" column. Note that lower case "mmmm" is used. Usually, you'd see "MMMM" as lower case "m" is reserved for minutes. Minutes now use lower case "n" as in "hh:nn", and "MMMM" doesn't work in this case.
For the DayName and YearMonth columns, dddd and yyyy mmm were used as custom string. Once that's done, you are all set and no Sort By columns will be needed.
Approach #2 – Zero-Width Spaces
This approach is adapted from an article on PowerBI.tips (here), in which a variable number of zero-width spaces are added as a prefix to the text you want sorted. You can use UNICHAR(8203) to get the zero-width space and the REPT( ) function to repeat it the desired number of times. Below is a DAX Date table that leverages this approach to create text columns for "Month" and "DayName" that sort in the correct order (according to the Date value).
Date_NoSort =
VAR BaseCalendar =
CALENDAR( DATE( 2020, 1, 1 ), DATE( 2021, 12, 31 ) ) //or use MIN() and MAX() with one of your table columns
RETURN
GENERATE(
BaseCalendar,
VAR basedate = [Date]
VAR yearnum =
YEAR( [Date] )
VAR monthname =
REPT( UNICHAR( 8203 ), 12 - MONTH( [Date] ) )
& FORMAT( [Date], "MMM" )
VAR dayname =
REPT( UNICHAR( 8203 ), 7 - WEEKDAY( [Date] ) )
& FORMAT( [Date], "ddd" )
RETURN
ROW( "Year", yearnum, "Month", monthname, "DayName", dayname )
)
In each case the desired test to be shown is created with FORMAT() and the number of zero-width spaces are generated with a simple expression. The "12-" and "7-" parts are added so that January and Sunday, respectively, end up with the highest number of zero-width spaces and show first in Ascending order.
For Your Convenience
You can download the pbix file used in the video above, or below are different ways to generate the Date table I usually use. You can use the same logic shown above in M too, with the Text.Repeat() and Character.FromNumber() functions. I've started to use Dataflows more lately, and this is a convenient way to provide a Date table to downstream report builders so they don't have to establish Sort By columns.
In addition to the columns discussed above, I like to add columns that show DaysFromNow, WeeksFromNow, MonthsFromNow, QuartersFromNow, and YearsFromNow. Not only are these useful as index columns in DAX expressions (as an alternate to Time Intelligence functions), but (as shown in the video) you can only use them as the axis/legend in visuals, or as a visual-level filter, to make your report more dynamic with each refresh.
DAX Table – GENERATE/ROW Style
Date_NoSort_Full =
VAR BaseCalendar =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ) //or use MIN() and MAX() with one of your table columns
RETURN
GENERATE (
BaseCalendar,
VAR basedate = [Date]
VAR yearnum =
YEAR ( [Date] )
VAR monthname =
REPT ( UNICHAR ( 8203 ), 12 - MONTH ( [Date] ) )
& FORMAT ( [Date], "MMM" )
VAR dayname =
REPT ( UNICHAR ( 8203 ), 7 - WEEKDAY ( [Date] ) )
& FORMAT ( [Date], "ddd" )
RETURN
ROW (
"Year", yearnum,
"Month", monthname,
"DayName", dayname,
"YearMonth",
yearnum & " " & monthname,
"YearQuarter",
yearnum & "Q"
& QUARTER ( [Date] ),
"YearWeek", yearnum & FORMAT ( WEEKNUM ( [Date] ), "00" ),
"WorkingDay", IF ( WEEKDAY ( [Date] ) IN { 1, 7 }, "N", "Y" ),
"DaysFromNow", DATEDIFF ( TODAY (), [Date], DAY ),
"WeeksFromNow", DATEDIFF ( TODAY (), [Date], WEEK ),
"MonthsFromNow", DATEDIFF ( TODAY (), [Date], MONTH ),
"QuartersFromNow", DATEDIFF ( TODAY (), [Date], QUARTER ),
"YearsFromNow", DATEDIFF ( TODAY (), [Date], YEAR )
)
)
DAX Table – ADDCOLUMNS Style
Date_NoSort_Full_AddCols =
VAR BaseCalendar =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ) //or use MIN() and MAX() with one of your table columns
RETURN
ADDCOLUMNS (
BaseCalendar,
"Year", YEAR ( [Date] ),
"Month",
REPT ( UNICHAR ( 8203 ), 12 - MONTH ( [Date] ) )
& FORMAT ( [Date], "MMM" ),
"DayName",
REPT ( UNICHAR ( 8203 ), 7 - WEEKDAY ( [Date] ) )
& FORMAT ( [Date], "ddd" ),
"YearMonth",
YEAR ( [Date] ) & " "
& REPT ( UNICHAR ( 8203 ), 12 - MONTH ( [Date] ) )
& FORMAT ( [Date], "MMM" ),
"YearQuarter",
YEAR ( [Date] ) & "Q"
& QUARTER ( [Date] ),
"YearWeek", YEAR ( [Date] ) & FORMAT ( WEEKNUM ( [Date] ), "00" ),
"WorkingDay", IF ( WEEKDAY ( [Date] ) IN { 1, 7 }, "N", "Y" ),
"DaysFromNow", DATEDIFF ( TODAY (), [Date], DAY ),
"WeeksFromNow", DATEDIFF ( TODAY (), [Date], WEEK ),
"MonthsFromNow", DATEDIFF ( TODAY (), [Date], MONTH ),
"QuartersFromNow", DATEDIFF ( TODAY (), [Date], QUARTER ),
"YearsFromNow", DATEDIFF ( TODAY (), [Date], YEAR )
)
M Table – With Zero-Width Spaces
let
StartDate = #date(2020, 1, 1), // or use List.Min and List.Max of a column in another query
EndDate = #date(2021, 12, 31),
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1, 0, 0, 0)),
InitialTable = Table.FromColumns({DateList}, {"Date"}),
#"Changed Type" = Table.TransformColumnTypes(InitialTable, {{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month", each Text.Repeat(Character.FromNumber(8203), 12 - Date.Month([Date])) & Text.Start(Date.MonthName([Date]), 3), type text),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "DayName", each Text.Repeat(Character.FromNumber(8203), 6 - Date.DayOfWeek([Date])) & Text.Start(Date.DayOfWeekName([Date]), 3), type text),
#"Inserted YearMonth" = Table.AddColumn(#"Inserted Day Name", "YearMonth", each Text.From([Year]) & " " & [Month], type text),
#"Inserted YearQuarter" = Table.AddColumn(#"Inserted YearMonth", "YearQuarter", each Text.From([Year]) & "Q" & Text.From(Date.QuarterOfYear([Date])), type text),
#"Inserted YearWeek" = Table.AddColumn(#"Inserted YearQuarter", "YearWeek", each Text.From([Year]) & Text.PadStart(Text.From(Date.WeekOfYear([Date])), 2, "0"), type text),
#"Added Custom1" = Table.AddColumn(#"Inserted YearWeek", "WorkingDay", each if List.Contains({0, 6}, Date.DayOfWeek([Date])) then "N" else "Y"),
AddDaysFromNow = Table.AddColumn(#"Added Custom1", "DayFromNow", each Duration.TotalDays([Date] - Date.From(DateTime.LocalNow())), Int64.Type),
AddWeeksFromNow = Table.AddColumn(AddDaysFromNow, "WeeksFromNow", each Number.RoundDown(Duration.TotalDays(Date.EndOfWeek([Date]) - Date.EndOfWeek(Date.From(DateTime.LocalNow()))) / 7, 0), Int64.Type),
AddMonthsFromNow = Table.AddColumn(
AddWeeksFromNow,
"MonthsFromNow",
each
let
today = Date.From(DateTime.LocalNow()),
thisdate = [Date]
in
(12 * Date.Year(thisdate) + Date.Month(thisdate)) - (12 * Date.Year(today) + Date.Month(today)),
Int64.Type
),
AddQuartersFromNow = Table.AddColumn(
AddMonthsFromNow,
"QuartersFromNow",
each
let
today = Date.From(DateTime.LocalNow()),
thisdate = [Date]
in
(4 * Date.Year(thisdate) + Date.QuarterOfYear(thisdate)) - (4 * Date.Year(today) + Date.QuarterOfYear(today)),
Int64.Type
),
AddYearsFromNow = Table.AddColumn(
AddQuartersFromNow,
"YearsFromNow",
each
let
today = Date.From(DateTime.LocalNow()),
thisdate = [Date]
in
Date.Year(thisdate) - Date.Year(today),
Int64.Type
)
in
AddYearsFromNowI hope this information can save you some time too and/or improve the functionality of your reports. Enjoy!
