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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.