Forum Discussion

MartinWPBI's avatar
MartinWPBI
Frequent Visitor
7 years ago
Solved

UK/US Date Format Weirdness

I have 3 columns in a visual, 1 shows in US date format and 2 show in UK date format (I am UK based as is this customer) the columns all point to the same column in a table and merely filter by the type of movement "G" being a receiving of stock and "S" being the sale of stock. The data source is a SQL database that I have alot of experience with and I know that the date therein is good.

 

First Received Date = CALCULATE(MIN(Movements[Date]), Movements[Movement Type] = "G")

 

First Sale Date = CALCULATE(MIN(Movements[Date]), Movements[Movement Type] = "S")

 

Any thoughts as to why I'm getting mixed formats? I've tried going into Query Editor and changing Type to Date > Using Locale English (United Kingdom) but as I anticpated it's not had any effect.

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Martin PBI,

     

    Solution is as you mentioned but you should to choose the date original format, It's mean "Type to Date > Using Locale English (United States)", this tool not convert the format It just identify the date local format from. PBI do the rest.

     

    Tell us if works for you.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Martin PBI,

     

    Solution is as you mentioned but you should to choose the date original format, It's mean "Type to Date > Using Locale English (United States)", this tool not convert the format It just identify the date local format from. PBI do the rest.

     

    Tell us if works for you.

     

    • MartinWPBI's avatar
      MartinWPBI
      Frequent Visitor
      7 years ago

      Hi Miltinho

       

      Thank you so much for your reply, I didn't realise that the function was to define the source format, very useful to know!

       

      Under your advice I've tried changing type to Date, English (United States) format and no change, I then tried Text English (United Kingdom) and no change. However changing to Text (United States) did then get the way that the columns appear to be consisten and in a UK format so thank you for leading me to the right path.

       

      The confusing thing for me is that these 2 columns in the visual did have differing formats yet both have the same source column in the data, they're merely filtered by the movement type  :smileyfrustrated:

       

      Martin

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        It's quite strange Martin... So doble check your date format source or your measures.
        Where data came from?... SQL?.
        If you add a file with a sample of your data I can try to understand what is going on.