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 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.

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!

3 Replies

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    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
      Icon for Super User rankSuper User
      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