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: Gett...