Making Dates Pop in Power BI: Adding Day Suffixes

Making Dates Pop in Power BI: Adding Day Suffixes


Problem Statement:

Have you ever wanted to display dates in your Power BI reports with a little extra flair? By default, Power BI offers some formatting options, but they can be limited. One common request is to add suffixes like "1st," "2nd," "3rd," or "4th" to the day of the month. Achieving this can be tricky, but fear not – we've got a solution!

Dax Formula:

DayFormatted = 

VAR DayNumber = DAY(DimDate[CalendarDate])

VAR Suffix =

    SWITCH(

        TRUE(),

        DayNumber = 1 || DayNumber = 21 || DayNumber = 31, "st",

        DayNumber = 2 || DayNumber = 22, "nd",

        DayNumber = 3 || DayNumber = 23, "rd",

        "th"

    )

RETURN

    CONCATENATE(CONCATENATE(CONCATENATE(DayNumber, Suffix), " "), FORMAT(DimDate[CalendarDate], "mmm"))

Result:



Explanation:

Here's how it works in simple terms:

Getting the Day Number: We start by figuring out the day of the month from our date data.

Finding the Right Suffix: Next, we use a special trick called SWITCH to determine which suffix ("st," "nd," "rd," or "th") should follow the day number.

Putting It All Together: Finally, we combine the day number, its suffix, and the month's abbreviation into a single neat package. This way, we get a date format that looks professional and is easy to understand.

By using this clever DAX code in your Power BI reports, you can make your date displays more engaging and user-friendly. It's a small touch that can make a big difference!

This solution not only makes your reports look better but also shows off the flexibility and creativity you can bring to your data presentations with Power BI.

Feel free to ask any questions about Power BI. I am here to help you.

Happy Learning! 😊

Comments

Popular posts from this blog

Create Calendar Table in Power BI using DAX Function

Calculate Sales for Last Year Month to date

CONTAINSTRING DAX