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