Sorting Months in a Calendar Year using Bold BI Expression

Published:

Sorting Months in a Calendar Year

When working with data that includes month names, it is often necessary to sort these months in the correct chronological order (January to December). Below are the expressions that can be used to convert month names into numeric values and then sort them accordingly.

Numeric Conversion of Months

To convert month names into numeric values (1 for January, 2 for February, etc.), the following SQL expression can be used:

CASE
    WHEN [Month] = 'January' THEN 1
    WHEN [Month] = 'February' THEN 2
    WHEN [Month] = 'March' THEN 3
    WHEN [Month] = 'April' THEN 4
    WHEN [Month] = 'May' THEN 5
    WHEN [Month] = 'June' THEN 6
    WHEN [Month] = 'July' THEN 7
    WHEN [Month] = 'August' THEN 8
    WHEN [Month] = 'September' THEN 9
    WHEN [Month] = 'October' THEN 10
    WHEN [Month] = 'November' THEN 11
    WHEN [Month] = 'December' THEN 12
    ELSE 0
END

This expression assigns a numeric value to each month, which can then be used for sorting.

Ordering Months Based on Numeric Values

Once the months have been converted to numeric values, the following expression can be used to order them back into their original names based on the numeric conversion:

CASE
    WHEN [MonthNumericConversion] = 1 THEN 'January'
    WHEN [MonthNumericConversion] = 2 THEN 'February'
    WHEN [MonthNumericConversion] = 3 THEN 'March'
    WHEN [MonthNumericConversion] = 4 THEN 'April'
    WHEN [MonthNumericConversion] = 5 THEN 'May'
    WHEN [MonthNumericConversion] = 6 THEN 'June'
    WHEN [MonthNumericConversion] = 7 THEN 'July'
    WHEN [MonthNumericConversion] = 8 THEN 'August'
    WHEN [MonthNumericConversion] = 9 THEN 'September'
    WHEN [MonthNumericConversion] = 10 THEN 'October'
    WHEN [MonthNumericConversion] = 11 THEN 'November'
    WHEN [MonthNumericConversion] = 12 THEN 'December'
    ELSE 'Invalid Month'
END