In this post you learn how to create ordinal numbers in both DAX and M. An Ordinal Number is a number that tells the position of something, such as 1st, 2nd, 3rd. An application where this is useful is when describing a date range like the 1st – 15th of April.
And where languages like M (in Power Query) and DAX offer numerous formatting options for dates, ordinal numbers are not included.
So how can you create this yourself?
Table of contents
1. Conditions for Ordinal Numbers
You can create ordinal numbers when you keep in mind a few conditions. To create a number suffix keep in mind that:
- numbers ending with 11, 12 or 13 end with “th” (11th, 111th, 12th, 13th, 113th)
- the remaining numbers ending with
- 1 end with “st” (1st, 21st, 101st)
- 2 end with “nd” (2nd, 22nd, 102nd)
- 3 end with “rd” (3rd, 53rd, 953rd )
- all other numbers end with “th” (5th, 14th, 25th)
Knowing all this, the rest of the post will show different methods to create ordinal numbers in both M and DAX. Some of them focused on readability and others on keeping the code short.
2. Create Ordinal Numbers in DAX
This chapter shows how you can create ordinal numbers using DAX. We will start with a descriptive example and then continue with a short alternative.
2.1. Method using SWITCH
Let’s say you have a report that shows month-to-date numbers for each date. You would like to add a column that shows the date range in which your numbers are calculated. So the 25th of April shows: “Revenue for the 1st – 25th of April 2022“.
Sam Fischer wrote about something similar in an article on Smart Narratives. To achieve this you can create a measure like the following:
Ordinal Numbers =
VAR LatestDate = MAX( Time[Date] )
VAR Last2Characters = RIGHT( DAY( LatestDate ), 2 )
VAR LastCharacter = RIGHT( DAY( LatestDate ), 1 )
VAR Addition =
SWITCH( TRUE( ),
Last2Characters IN { "11", "12", "13" }, "th",
LastCharacter = "1", "st",
LastCharacter = "2", "nd",
LastCharacter = "3", "rd",
"th"
)
VAR Result =
"Revenue for " & " 1st - "
& FORMAT( LatestDate, "D" )
& Addition
& " of "
& FORMAT( LatestDate, "mmmm yyyy" )
RETURN Result
What the code does is:
- first it finds the latest date in the current filter context.
- based on this date it extracts both the last character of a number and the last 2 characters of a number into two separate variables.
- the switch statement then assigns:
- “th” to numbers where last 2 characters are one of 11, 12 or 13.
- “st”, “nd” and “rd” to numbers ending in 1, 2 or 3 respectively.
- “th” to all other values.
- the result variable then concatenates all the required text elements together to get to the final result.
2.2. Method using MID
A more complex method to create ordinal numbers is based on an approach Rick Rothstein pointed me to on Linkedin. Below is an adapted version for DAX:
Ordinal Numbers MID =
VAR LatestDate = MAX( 'Time'[Date] )
VAR DayNum = DAY( LatestDate )
VAR Addition = MID("thstndrdth", MIN(9, 2 * RIGHT( DayNum ) *
(MOD( DayNum -11, 100) >2) +1), 2)
VAR Result =
"Revenue for " & " 1st - "
& DayNum
& Addition
& " of "
& FORMAT( LatestDate, "mmmm yyyy" )
RETURN Result
This code is very similar to the previous one, except for the Addition variable. The addition variable uses the MID function. That function:
- has the list of suffixes as string in the first argument (th, st, nd, rd, th). Notice that “th” appears there twice.
- The second argument contains logic to find out which suffix is relevant for a number. When writing this logic out step-by-step in a spreadsheet, I could follow the steps but was still left confused. Most important here is to remember this step provides the position in the string where the suffix starts.
- the third argument then returns the first 2 characters after the suffix starting position of step 2.
This code is one of the shortest ways to create ordinal numbers. The downside to it is that it is very difficult to understand. For the sake of understanding your model, you shall have to decide whether to prioritize readability or a shorter code.
3. Create Ordinal Numbers in M
My preferred way to work with Ordinal Numbers is to add these to a date table. So next to a date you will also find which position the date has. A date on the first of the month becomes 1st, and day 22 becomes 22nd. After adding these in the date table, the work in DAX is very simple.
Creating Ordinal Numbers in Power Query takes a similar approach with different functions. You will learn two ways to categories of these in Power Query.
The first category focuses on number series and the second on date series. The difference is important because Power Query requires you to explicitly deal with data types.
3.1. Method for Number Series
The more descriptive way to create ordinal numbers is by writing conditional if statements. If you are new to these, here you can read more on if-statements in Power Query.
To create ordinal numbers for a series of numbers, you can create a custom column with:
let
Last2Characters = Text.End( Number.ToText( [Index] ), 2 ),
LastCharacter = Text.End( Number.ToText( [Index]), 1 )
in
if List.Contains( { "11", "12", "13" }, Last2Characters ) then "th"
else if LastCharacter = "1" then "st"
else if LastCharacter = "2" then "nd"
else if LastCharacter = "3" then "rd"
else "th"
An alternative approach was provided by Sergei Baklan. You can paste below code in a custom column to reach a similar result.
let
suffix = {"th", "st", "nd", "rd"},
a =
if Text.End(Text.From( Number.IntegerDivide( [Index], 10) ), 1) = "1"
then suffix{0}
else suffix{Number.Mod([Index], 10)}?
in
a??suffix{0}
3.2. Method for Date Series
You can also create ordinal numbers in M for Dates. By saving the values to your Date table, you don’t have to generate these in DAX. Our goal is to create the suffix from the Ordinal Suffix column:
If you are creating ordinal numbers for a series of dates in a calendar table, you can create a custom column that states:
let
Daynumber = Date.ToText( [Date], "dd" , "nl-NL"),
LastCharacter = Text.End( Date.ToText( [Date], "dd" , "nl-NL"), 1 )
in
if List.Contains( { "11", "12", "13" }, Daynumber ) then "th"
else if LastCharacter = "1" then "st"
else if LastCharacter = "2" then "nd"
else if LastCharacter = "3" then "rd"
else "th"
This code:
- first creates the variables Daynumber and LastCharacter, which we will reference in later steps
- the code then checks
- whether the day number is one of 11, 12 or 13 and returns “th”
- whether the last character ends with one of 1, 2 or 3 and then returns 1, 2 or 3 respectively.
- all other numbers get “th” as suffix.
If you like, you can consolidate part of the code by writing:
let
Daynumber = Date.ToText( [Date], "dd" , "nl-NL"),
LastCharacter = Text.End( Date.ToText( [Date], "dd" , "nl-NL"), 1 )
in
if List.Contains( { "11", "12", "13" }, Daynumber ) then "th" else
if List.Contains( { "1", "2", "3" }, LastCharacter ) then
{"st", "nd", "rd"}{Number.From(LastCharacter) -1 } else "th"
The highlighted part of the code extracts the suffix from a list. The extraction is done based on the index position, which equals the character number – 1.
3.3. Method using Text.Middle
Lastly, you can also create ordinal numbers using Rick Rothstein’s method as mentioned in step 2.2.
Text.Middle( "thstndrdth",
List.Min(
{ 8,
2 * Number.From ( Text.End( Date.ToText( [Date], "dd"), 1 ) ) *
(Number.From( Date.Day( [Date] ) - 11 - 100 *
Number.RoundDown( ( Date.Day( [Date] ) -11 ) / 100 ) > 2 ) ) } ),
2 )
The initial benefit of this example was that it can be written in a single line of VBA or Excel formulas. This benefit also stands for the DAX code in chapter 2.2.
However in Power Query it’s a whole different story. The code here is longer because of the explicit type conversion. And besides, it is also much more complex than the other examples. My recommendation would therefore be to use one of the earlier methods.
You can find all methods in the below download file:
Enjoy!
Recommend Reading >>> Power Query - Foundations <<< ፠ List Functions (200+ examples) ፠ Text functions (150+ examples) ፠ Creating Tables from Scratch (40+ examples) ፠ Understanding If Statements ፠ Generating a Date Table >>> Power Query - Advanced Topics <<< ፠ Master List.Generate with Easy Examples ፠ Deep Dive into List.Accumulate ፠ Create Calendar with Dynamic Language ፠ Replacing Values (User Interface and Beyond)
Hello,
how does “??” operator works in Sergei Baklan’s solution? never seen that one
Hi Jakub,
The ?? operator works like the COALESCE function in SQL.
It takes the first value before the question marks, and if that one equals ‘null’ it will return the item after the ??.
Let’s say you use below expression:
If the [Sometext] column returns null, the formula returns the number in the [Somenumber] column.
Edit: You can find my article on it here: https://gorilla.bi/power-query/coalesce/