UK/US Date Format Weirdness

[MartinWPBI]

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.

[Anonymous · Accepted solution]

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]

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 

Martin

[Anonymous]

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.

[MartinWPBI]

Hi Miltinho

It's a very large database and this is a particularly large table recording movements of every item however I do know that the movement dates are in a SQL datetime format and consistent with a UK format. However the format now looks good on the visual and the customer is happy so I'm happy to put this one to bed and get on with the next load of work.

My thanks again for your kind assistance,

Martin