AUS Date Chnged to US in Power Pivot Data Model

Anything related to PowerPivot and DAX Formuale
cjonesreadle
Posts: 2
Joined: Mon Aug 28, 2017 2:23 pm

AUS Date Chnged to US in Power Pivot Data Model

Postby cjonesreadle » Thu Aug 09, 2018 4:29 pm

I have added 2 tables to the Power Pivot Model that both have dates included in the data. The dates are in the correct Australian format dd/mm/yy within the excel table, however somehow they have changed to the US format mm/dd/yy with in the Power Pivot Data Model and are displaying in this format when I create a Pivot Table.

I have attached examples for you to see what is happening

Has anyone experienced this before and how can I fix it?

Thanks
Cara
Attachments
PowerPivot Dates.docx
(295.67 KiB) Downloaded 22 times

MattAllington
Posts: 993
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: AUS Date Chnged to US in Power Pivot Data Model

Postby MattAllington » Fri Aug 10, 2018 7:57 am

I assume your PC is configured for the AUS region. Assuming so, try loading the data using Power Query. In Power Query, there is an option to change the data format "Change Type\Using Locale".

Discover more about my Dax Book.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

cjonesreadle
Posts: 2
Joined: Mon Aug 28, 2017 2:23 pm

Re: AUS Date Chnged to US in Power Pivot Data Model

Postby cjonesreadle » Fri Aug 24, 2018 10:51 am

Hi Matt

I have been using Power Query to pull the data into the Data model and went through and changed the Locale to Australia. This has helped with the pivot tables how ever when I change the tables to Cube formulas, the date change back to the US formatted dates again.

I have included screen shots to show the differences.

Thanks
Cara
Attachments
PowerPivot Dates.docx
(517.85 KiB) Downloaded 8 times

Kamran_bsh
Posts: 10
Joined: Wed Jul 25, 2018 8:54 am
Location: England

Re: AUS Date Chnged to US in Power Pivot Data Model

Postby Kamran_bsh » Sat Aug 25, 2018 5:39 am

Excel settings are different from those of power query. You can change your regional settings temporarily in control panel and that will fix the problem. Or you can change locale from excel.
Remember you need to keep USA settings, not AUS because your data is in US format.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 4 guests