Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

Locale doesn't work on date format for table created in PBI

When I create a new table and I use the CALENDAR dax function my date gets created using my locale (cdn) and not the locale that I have set in PBI (US) for this data.  When I try to change the date so that it is mm/dd/yyyy, that value is not even available.  If the data gets pulled into the data model with the locale being set in the query I do not have this issue. Everything looks good.  This is the same issue I ran into using Excel Power Query and Excel Power Pivot.  If I do not have the correct format then wherever these dates are used they don't display correctly

.menu doesn't contain correct formatmenu doesn't contain correct format


Hi @BerniceS,


Based on my understanding, you load the date in extre file, and the locale(cdn) of the date in the file is different from locale(US) of Power BI destop? But I don't find the locale(cdn) in Power BI desktop as the highlighted in red line shown in screenshot below.


For your issue, it may be caused by the incompatibility of date type for different locale, Please change them to one locale before you use them. Here is a similar theard.


Best Regards,

Hi Angelia, Actually, that is not what I said.  You can see from my screen print that I am creating the date directly in PowerBI as a column using a DAX formula.   The data set that I am using is formatted as English(United States).  When I import data that has a date column, and I add as a step in the query Change Type (locale), the date works great.  I can format it exactly as I want.  However, using the add column method does not work regardless of whether I do or, do not create the column with the 'Regional Setting for This File' set to English(United States).  It always uses my locale which is English(Canada).  The option for the requested format is not even there and it should be.   I want and need MM/DD/YYYY not MM/DD/YY.


If you had a PC that you could set to a locale of English(Canada) and tested this out you would see what I am talking about.  It is an issue both in PowerBI and Excel's PowerQuey/PowerPivot.


There should either be a work around that works or a product fix that recognizes the locale for the data set and acts accordingly.


Thank you.

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors