Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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

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?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

2 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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?