Forum Discussion

lucaspisso's avatar
lucaspisso
Frequent Visitor
1 year ago
Solved

Issue with Date Format Interpretation in Power BI

Hello everyone, I'm reaching out to report a recurring issue related to the interpretation of date formats in Power BI. Specifically, it seems that in some cases Power BI is confusing days and month...
Need Help
  • mark_endicott's avatar
    mark_endicott
    1 year ago

     

    lucaspisso - First thing to check is your Locale for import.  

    Navigate to Options and Settings > Current File > Regional Settings and ensure this is set to your Locale:


     

    Next up, you can overwride this at a column level in Power Query:

    • Transform data > Transform Editor (Power Query Editor). 
       
    • Select the date column you want to format. 
       
    • Right-click on the column and choose Change Type > Using Locale. 
       
    • In the window that appears, specify the Data Type as Date or DateTime, and then select the appropriate Locale from the dropdown list. 
       
    • This allows you to override the default locale for specific columns. 

     

    If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi mark_endicott,

     

    Changing this setting only affects future data loads, not already loaded data.

     

    lucaspisso, alogwith changing this setting as mentioned by mark_endicott , You should perform below steps in Power Query

    Convert Text to Date in dd/mm/yyyy format

    1. Open Power Query Editor

    2. Select the column

    3. Right-click > Change Type > Using Locale...

    4. Set:

      • Data Type: Date

      • Locale: English (United Kingdom) (or appropriate)

    This forces correct parsing of the text into date values using your intended format.

     

    Format Display of Dates (for visuals)

    While this won’t affect parsing, you can format how dates appear:

    • In the Data pane, select your Date column

    • Under Column tools > Format, choose dd/mm/yyyy

    This is for visual consistency, not parsing logic.

     

    Power BI Service Consideration

    When publishing to Power BI Service:

    • Report viewers see date formats based on their browser or Power BI profile locale

    • To enforce consistency:

      • Set report-level language:
        File > Options > Regional Settings > Report Locale

      • Or direct users to set Power BI Service locale in their settings

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi lucaspisso ,
Thanks for reaching out to the Microsoft fabric community forum.

Set Power BI Desktop Regional Settings
Go to: File - Options and settings - Options - Regional Settings
Under “Regional Settings”, set the locale to:
   English (United Kingdom) – this uses dd/mm/yyyy by default

 

Use Power Query – Change Type With Locale
Open Power Query Editor (Transform Data)
Right-click your Date column - choose “Change Type” - “Using Locale”
In the dialog box:
Data Type: Date
Locale: English (United Kingdom) (or any dd/mm/yyyy locale)
Click OK

 

Avoid Auto Conversion Errors From Source
If dates come from CSV or Excel as text, Power BI might auto-convert incorrectly.
Prevent auto-type detection if possible
Always use “Change Type with Locale” in Power Query


Format Date Column in Report View
Go to Data View (table icon)
Select your date column
Under “Modeling” tab - Format - Choose: dd/mm/yyyy

 

Optional: Format in DAX (for display purposes only)-To show date as text in visuals (not for filtering):

 

Formatted Date = FORMAT([YourDateColumn], "dd/mm/yyyy")

This becomes a text column, not ideal for filtering or sorting.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.Best Regards,
Tejaswi.
Community Support