'How to assign 1 st, 2 nd,3 rd etc based on number in date filed using power bi DAX formula?

How to assign automatically st/nd/rd/th to number behind in date field using power bi DAX formula.

I'm creating labels for date something like this 1st MAY 2020but if we have 2 may then it would be 2nd may likewise, for this how we create

Solution 1:^[1]

We have to use DAX SWITCH() function to resolve this case like below example

Sub Heading =

"Status for" & " " & MAX('People Registered'[Last Refresh Time]) & " " & "JKT" & " " & FORMAT(DATEVALUE(MAX('People Registered'[DATE])) DD") & SWITCH(DAY(MAX('People Registered'[Last Refresh Date])), 1,"st", 2, "nd", 3, "rd", 4, "th" , 21, "st", 22, "nd", 23, "rd", 24, "th" , 31, "st" ) & " " & FORMAT(DATEVALUE(MAX( 'People Registered'[Last Refresh Date])),"MMM") & " " & FORMAT(DATEVALUE(MAX('People Registered'[Last Refresh Date])),"YYYY")

Output:

Solution 2:^[2]

You are able to add an ordinal number by using the following DAX:

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 = DayNum & Addition
RETURN Result

Hope that helps!

Rick

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	KARTHIKEYAN.A
Solution 2	Rick de Groot - gorilla.bi

'How to assign 1 st, 2 nd,3 rd etc based on number in date filed using power bi DAX formula?

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]