Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

DAX FORMAT() date/time formatting problem

I have a datetime column (table Issues, column CREATED) which I want to display (in a table visual) in this format:

   'dd/mm/yy h:nn AM/PM'.

Following the documentation of the FORMAT funcrion, I created a new column with this formula:

Create_Slashes = FORMAT(Issues[CREATED],"dd/mm/yyyy h:nn AMPM")
When I add the column to a table visual (next to the original CREATED column), the intended slashes are replaced with dashes:
 
If I go to the table in the data view, I see that the data type of the new column is Text.
If I change it to date/time, then a default format is applied ('mm/dd/yyyy h:nn:ss AM/PM'), which defeats the purpose my FORMAT() invocation.
 
Any clue?
Need Help

8 Replies

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Anonymous 

    Have you tried selecting the datetime column --> Column Tools and in format box copy/paste your desired format

    dd/mm/yyyy h:nn AMPM

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks, but as I mentioned earlier, this cannot be done since this column is a measure, so when in the data view, the format of the measure is locked to Text and cannot be changed.

      Just to re-iterate, the whole purpose of this measure is to dynamically invoke the FORMAT() function which takes the format from another column in another table.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you. I am aware of that and of course it works, but that is not the solution.

    What I want is to control the format of the column by the FORMAT() function in the report view and not by the column's data type in the data view.

    The reason is that I am changing the column format dynamically by a measure that relies on a slicer (see this post here: https://community.powerbi.com/t5/Community-Blog/Custom-Date-Formats-in-Power-BI-Using-a-Slicer/ba-p/1642519).

    This method works for different formats, but for some reason, for formats that use '/' as the date delimiter, the '/' is replaced by a '-'. Only the delimiter is wrong, the rest of the fields are exactly as I specify in the format.

    For example, for 'mm/dd/yyyy h:nn AMPM', the display is '01-27-2022 3:48 PM'.

    • themistoklis's avatar
      themistoklis
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Anonymous 

       

      I tried to replicate your issue but in the latest version of PowerBI desktop (December 2022) that im using ... the application was not replacing '/' with '-'.

       

      Try installing the latest version and check whether the issue still exists

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks. My installed version is 2.112..603.0 64-bit (December 2022), which seems to be the latest. When I go to the Microsoft Store, it does not show an Update button, only Open, which opens the desktop app.

        What version does your desktop app display?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please have a try.

    Change the custom column in the Model.

     

     

    Best Regards
    Community Support Team _ Polly

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks Polly,

      I forgot to mention a crucial detail: this column is a measure, as described in the blog post that I linked to in my question.

      It seems that measures cannot be formatted with the method you've described - the 'Format' is set to Text and cannot be changed.

       

      Here is the measure:

      CreatedRegional = 
      var formatter = SELECTEDVALUE('Date Formats'[Format])
      var source_date = SELECTEDVALUE(Issues[CREATED])
      RETURN
        FORMAT(source_date, formatter)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Anonymous ,

        We really can't modify the type of measure. Please try to use column or you can create an idea here.

        Ideas (powerbi.com)

         

        Best Regards
        Community Support Team _ Polly

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.