Date to Text Format Differences - Causing Me a Big Problem

[MJG2112]

I have a set of reports that create composite keys using a numeric value and date.  It's very basic but serves my needs.

CompositeKey = MyTable[EntityID] & "-" & MyTable[Reporting Month]

This results in a value like 123456-01/01/2026

I am based in the UK.  My colleague based in India has created some reports using the same composite key DAX expression, however this results in a value like 123456-01-01-2026.

We have a need to use Direct Access queries across reports usings this key, which obviously doesn't work because of the formatting difference.  I've changed the locale in the Regional Settings to English (United Kingdom) in my colleague's reports, but this hasn't made any difference.  It seems it's something more fundamental than that and I can't figure out what I need to change to align the date to text format.

[MJG2112 · Accepted solution]

hi @mizan2390 that worked with the addition of a locale parameter at the end of the FORMAT:

CompositeKey = MyTable[EntityID] & "-" & FORMAT(MyTable[Reporting Month], "dd/MM/yyyy", "en-GB")

[mizan2390]

@MJG2112 , great to know that it helped you to reach to a solution.

[mizan2390]

hi @MJG2112 

The issue you are experiencing is caused by DAX's implicit automatic type conversion when using the & concatenation operator. When you concatenate a date with a string, DAX automatically converts the date into text relying on the local machine's default system settings. 

Can you please try the DAX below and see if this solves your problem.

CompositeKey = MyTable[EntityID] & "-" & FORMAT(MyTable[Reporting Month], "dd/MM/yyyy")

If this solves your problem, please give a kudos and mark this as solved.