Forum Discussion

maurcoll's avatar
maurcoll
Helper IV
1 year ago
Solved

entering uk date format

Hi 

i am having an issue with date formatting, i have a measure where i have created a variable for a start date set at "01/05/2024" as this won't change.
The issue is if i use the UK format as detailed the result is incorrect i have to use the US format of "05/01/2024".

Does anyone know how i can change this, i have checked the global regional settings and is set to default, it doesnt give me an option to change to English (United Kingdom), the current file regional settings are set to import English (United Kingdom)
All of my date columns are set to UK.

My computer settings are UK.
I can use Date(2024,5,1) and that works but just wondering why if i type in "01/05/2024" it doesnt work

Need Help
  • Cookistador's avatar
    Cookistador
    1 year ago

    Hi maurcoll 

     

    This issue is related about how DAX works, DAX treats literal date strings in US format (MM/DD/YYYY) (it is not regarding your system settings)

    So this is why the Date(2024,5,1)  approach works 

3 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    Hi maurcoll 

     

    This issue is related about how DAX works, DAX treats literal date strings in US format (MM/DD/YYYY) (it is not regarding your system settings)

    So this is why the Date(2024,5,1)  approach works 

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    Hey maurcoll ,

    You're encountering this behavior because DAX interprets hardcoded (literal) date strings in US format (MM/DD/YYYY) regardless of your system, file, or Power BI regional settings.

     

    Why It Happens

    Even if:

    • your Power BI file regional settings are set to English (United Kingdom),

    • your PC’s locale is UK,

    • and all columns show correctly formatted UK dates,

    DAX treats string literals like "01/05/2024" as US format, meaning it assumes MM/DD/YYYY. So "01/05/2024" is interpreted as January 5, 2024, not May 1.

    This behavior is by design for DAX and not a bug or regional settings issue.

     

    Recommended Fix

    To avoid this ambiguity and ensure correctness, use the DATE() function:

    VAR StartDate = DATE(2024, 5, 1)

    This always produces 1st May 2024 correctly independent of locale.

    Avoid using: VAR StartDate = "01/05/2024"

     

    Unless the string is parsed explicitly with a function like DATEVALUE, which also might still default to US format unless the column or context enforces another locale.

     

    If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


    Best Regards,
    Nasif Azam