Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Do you want to make your data more meaningful by adding ordinal numbers? An ordinal number is a number that tells you the position or order of something in relation to other numbers, like first, second, third and so on.

But, there’s no easy built-in function available in DAX or Power Query to generate these values. That’s why in this article, I will show you how to create ordinal numbers using both DAX and Power Query. So you can easily include them in your Date Table.

You’ll learn how to add suffixes like “st”, “nd”, “rd” and “th” to numbers to create ordinal numbers. It might seem a bit tricky at first, but by following the examples, you’ll be able to make your data more meaningful in no time! Let’s get started!

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“.

DAX Ordinal Numbers Result

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:

  1. first, it finds the latest date in the current filter context.
  2. 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.
  3. the switch statement then assigns:
    • “th” to numbers where the 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.
  4. 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:

  1. has the list of suffixes as a string in the first argument (th, st, nd, rd, th). Notice that “th” appears there twice.
  2. The second argument contains logic to determine 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 confused. The most important here is to remember this step provides the position in the string where the suffix starts.
  3. 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 the 22nd. After adding these to 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 categorise of these in Power Query.

The first category focuses on the number series, and the second on the date series. The difference is important because Power Query requires you to deal with data types explicitly.

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 making use of Text.End, Number.ToText and List.Contains:

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"
Ordinal Suffix for Number Series

An alternative approach was provided by Sergei Baklan. You can paste the below code in a custom column, making use of the functions Number.IntegerDivide and Number.Mod 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:

Power Query Ordinal Numbers Result

If you are creating ordinal numbers for a series of dates in a calendar table, you can create a custom column that makes use of the Date.ToText function:

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:

  1. first creates the variables Daynumber and LastCharacter, which we will reference in later steps
  2. 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 could 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.

Download

You can find all methods in the below download file:

Conclusion

That’s it! Now you know how to create ordinal numbers in DAX and Power Query. You’ve learned how to create ordinal numbers with both DAX and Power Query, and you can use them in your own projects to make your data more meaningful.

I hope this article was helpful and you’re now more comfortable creating ordinal numbers in DAX and Power Query. Interested in other interesting articles? Make sure to read how to create a column that always returns the current month in your slicer.

Enjoy!

Share on:
    • 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:

       each [Sometext]??[somenumber]

      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/

      Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.