Format Function isn't accepting DAX custom date/time characters

[Anonymous]

I have a Date/Time field in this format: "6/13/2017 1:47:00 PM"

I want to create a new column that displays in this format: "1pm"

The DAX function reference for Format suggests that the way to do it would be with the format string "htt", so my function looks like this:

Hour Created = FORMAT('DataTable'[Created DateTime], "htt")

But it doesn't recognize "tt" and instead of "1PM", it returns "13tt". Won't take "t" either. Also, in testing, it doesn't respect "gg", "z", "zz", or "zzz". It also treats minute characters "m" and "mm" the same as month characters "M" and "MM" (so not sure how you would isolate minutes), "y" displays the numeric day of the year, and "yyy" seems to display number of days since June 1970.

Is there a different reference sheet for the Format function as it is used in Power BI?

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

To get hour in format "1PM", please try below formula:

Hour Created = FORMAT('DataTable'[Created DateTime], "h AM/PM")

Also, using AM/PM, am/pm, A/P, a/p can return expected result.

Best regards,
Yuliana Gu

[Anonymous]

That did that trick. Where did you find it? Is there a better reference for the Power BI Format function? What would you use for minutes, since both lower and upper-case m and mm refer to month?