Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BerniceS
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

2 REPLIES 2
v-huizhn-msft
Employee
Employee

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.

 

1.PNG
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,
Angelia

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

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.