Issue with Date Format Interpretation in Power BI

[lucaspisso]

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 months when parsing dates.

For example, when working with a dataset containing the date 02/01/2025 (2nd January 2025), the system sometimes interprets 03/01/2025 as March 1st. This inconsistency is causing problems in data visualization and reporting.

Could you kindly advise on how to enforce a consistent date format (preferably dd/mm/yyyy) across the entire report or dataset, and whether this should be handled in Power Query, in the data source, or via Power BI regional settings?

Thank you in advance for your support.

[mark_endicott · Accepted solution]

@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!

[v-tejrama]

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

[mark_endicott · Accepted solution]

@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 · Accepted solution]

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!